Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse Blank Records (97 SR2)

    Greetings!

    I've got a lengthy process which imports data from several spreadsheets. Once this import is complete, I'm left with chunks of solid data, separated with blank rows.

    Is there a way for me to find and delete these blank rows in VBA?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    In Access, run a delete query with as criteria Is Null for the fields that require data (eventually all fields).
    Francois

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    Is this Access or Excel?

    If it is Excel, the answers is certainly yes. However, you will need to provide more information.

    1- Is there is column that is never empty if the entire row is not empty?

    2- How do you define an empty row? Do multiple cells have to be checked? If so , which ones?
    Legare Coleman

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    The following will delete all rows in the current region where the contents of the cell in the first column is blank :<pre> Sub DeleteBlanks()
    Dim oCell As Range
    For Each oCell In ActiveCell.CurrentRegion.Columns(1).Cells
    If oCell.Value = "" Then
    oCell.EntireRow.Delete
    End If
    Next
    End Sub</pre>

    You could also sortt the range, which should have the effect of removing the blanks.

    Andrew C

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    I'm finding that this does work somewhat OK, in that it does what it should to a degree, however it does not cycle through the entire spreadsheet. Instead it deletes only the first non-blank row...

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Parse Blank Records (97 SR2)

    I would guess Andrew meant to use ActiveSheet instead of ActiveCell. Also, you might try UsedRange instead of his CurrentRegion.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    John, I don't know what I actually meant at the time but was not thinking straight. I should have used both your suggestions, ActiveSheet and UsedRange. Correction posted

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    It's still missing a few, but I find I can use the data/sort options to do sort of the same thing...

    Now, how might I parse out duplicate Keys? Column A contains a list of numbers, if the number is duplicated anywhere else, I'd like that row deleted.

    Any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    Drk,

    Try <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=42628&Search= true&Forum=xl&Words=duplicates&Match=And&Searchpag e=0&Limit=25&Old=allposts&Main=42583>this post</A> from Legare for deleting duplicates.

    Andrew

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    Again sorry, I forgot to post <pre> Sub DeleteBlanks()
    ActiveSheet.UsedRange.Columns(1). _
    SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub</pre>

    However if you are using the sort method that should be ok.

    The above code should delete all rows if the first column contains a blank.

    Andrew

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Parse Blank Records (97 SR2)

    I perpetuated the error of going from beginning to end instead of end to beginning, so that any time there are a pair of blank rows the second one got skipped. Shame on me considering I did this same thing just a few days ago. Back to the drawing board.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    Yup, that is why I asked those questions.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse Blank Records (97 SR2)

    I can't help you directly, however I know that Chip Pearson's site at <A target="_blank" HREF=http://www.cpearson.com>http://www.cpearson.com</A> has all the answers to these and many other questions.

    I hope that it helps

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Parse Blank Records (97 SR2)

    So now that the thread is pretty much dead, this code will ensure that each row is truly blank and delete blank rows in any user selection. I think.

    Sub DeleteBlankRows()
    Dim intUsedColCount As Integer, intCalcSet As Integer
    Dim lngLastUsedRow As Long, lngCtr As Long
    If Worksheets(ActiveSheet.Name).ProtectContents = False _
    And TypeName(Selection) = "Range" Then
    Application.ScreenUpdating = False
    intCalcSet = Application.Calculation
    Application.Calculation = xlCalculationManual
    intUsedColCount = ActiveSheet.UsedRange.Columns.Count
    lngLastUsedRow = _
    IIf(ActiveSheet.UsedRange.Rows.Count < Selection.Rows.Count + Selection.Row - 1, _
    ActiveSheet.UsedRange.Rows.Count, Selection.Rows.Count + Selection.Row - 1)
    For lngCtr = lngLastUsedRow To Selection.Row Step -1
    If Cells(lngCtr, Selection.Column).Value = "" Then
    If Rows(lngCtr).SpecialCells(xlCellTypeBlanks).Count = _
    intUsedColCount Then Rows(lngCtr).Delete
    End If
    Next lngCtr
    Application.Calculation = intCalcSet
    Application.Calculate
    Application.ScreenUpdating = True
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parse Blank Records (97 SR2)

    Dead threads are fun! Good job, John! I especially like the TypeName(Selection) error check. It avoids chart sheets, embedded objects, etc. I need to remember that. However, being the teacher type, I just wanted to point out that Excel's Intersect method can simplify your code (yes, there is a Union method, too). Here's the same routine from a little different perspective:<pre>Option Explicit
    Sub DeleteBlankRows()
    ' Deletes any blank row in the selection
    Dim rng As Range
    Dim calSave As Long, i As Long
    If ActiveSheet.ProtectContents Then Exit Sub
    If TypeName(Selection) <> "Range" Then Exit Sub
    calSave = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Set rng = Application.Intersect(Selection, ActiveSheet.UsedRange)
    If rng Is Nothing Then Exit Sub ' selection outside of used range
    For i = rng.Rows.Count To 1 Step -1
    If Application.CountA(rng.Rows(i).EntireRow) = 0 Then _
    rng.Rows(i).EntireRow.Delete
    Next i
    Application.Calculation = calSave
    Application.ScreenUpdating = True
    Application.Calculate
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •