Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting blank rows

    I need a few lines of code that delete the blank rows from a worksheet. I am currently using the code below, but this does not seem to always work

    In the following code Modlastcells.LastCellInWS(ws).Row correctly finds the last row in the sheet

    Can someone please assist with a few lines of code

    Thank you
    Code:
    Dim MyRange As Range
    Dim iCounter As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Time Report")
    'Step 2: Define the target Range.
    Set MyRange = ws.Range("A1:C" & Modlastcells.LastCellInWS(ws).Row)
    'Step 3: Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 1 Step -1
    'Step 4: If entire row is empty then delete it.
    If Application.CountA(Rows(iCounter).EntireRow) = 0 Then
        Rows(iCounter).Delete
    End If
    'Step 5: Increment the counter down
    Next iCounter
    Last edited by RetiredGeek; 2014-03-11 at 08:17. Reason: Added Code Tags

  2. #2
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    This is my code - itr deletes all lines with a number less than or equal to zero - I'm sure you can work it out:
    wodata is obtained from a "Set wodata = worksheets(the tab I am interested in)"

    Code:
    '
    '*** now delete lines with no totals
    '
            For im = 2 To lastline - 1 ' lastline is the last line with data in - got from earlier on
                If wodata.Cells(im, countrycol + 1).Value <= 0 And Len(wodata.Cells(im, countrycol + 1)) > 0 Then
    '
    '** select the row - delete it and change pointers to match  
    '          
                    Rows(im & ":" & im).Select
                    Selection.Delete Shift:=xlUp
                    im = im - 1
                    lastline = lastline - 1
                    
                End If
            Next im

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    pergunas,

    Modifying your code slightly works.

    HTH,
    Maud

    Code:
    Public Sub DelRows()
    Dim MyRange As Range
    Dim iCounter As Long
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Time Report")
    'Step 2: Define the target Range.
    Set MyRange = ws.UsedRange
    'Step 3: Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 1 Step -1
    'Step 4: If entire row is empty then delete it.
    If Application.CountA(Rows(iCounter).EntireRow) = 0 Then
        Rows(iCounter).Delete
    End If
    'Step 5: Increment the counter down
    Next iCounter
    End Sub

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..whenever I use a routine to delete rows, I turn calcs to manual first, then back to automatic when finished.
    You would not believe the speed improvement!!1

    zeddy

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    Yes, I can see the benefit.

Posting Permissions

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