Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Count rows and delete blank rows

Hi, im looking for a string of code which will count till the last row and then delete any blank rows

I dont want the code to loop through each row as i have 180k rows

Many thanks
0
Seamus2626
Asked:
Seamus2626
  • 2
2 Solutions
 
MacroShadowCommented:
Loop thru the rows and delete the empty ones:
Sub DeleteBlankRows()

    Dim rng As Range
    Dim lngCounter As Long
    
    Set rng = Range("A1:F180000") ' Change to match you range
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

        For lngCounter = rng.Rows.Count To 1 Step -1
            If WorksheetFunction.CountA(rng.Rows(lngCounter)) = 0 Then
                rng.Rows(lngCounter).EntireRow.Delete
            End If
        Next i
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Open in new window


Or move the empty rows to the bottom:
If sheet has a header row:
Sub MoveBlankRowsToBottom()

    Dim rng As Range

    Set rng = Range("A1:F180000")    ' Change to match you range

    With rng
        .Sort Key1:=.Cells(2, 1), _
              Order1:=xlAscending, _
              Header:=xlYes, _
              OrderCustom:=1, _
              MatchCase:=False, _
              Orientation:=xlTopToBottom
    End With

End Sub

Open in new window

If sheet doesn't have a header row:
Sub MoveBlankRowsToBottom()

    Dim rng As Range

    Set rng = Range("A1:F180000")    ' Change to match you range

    With rng
        .Sort Key1:=.Cells(2, 1), _
              Order1:=xlAscending, _
              Header:=xlNo, _
              OrderCustom:=1, _
              MatchCase:=False, _
              Orientation:=xlTopToBottom
    End With

End Sub

Open in new window

0
 
MacroShadowCommented:
I'm not sure what you mean by counting rows. What do you want to count, the empty rows, the non-empty rows, get the last used row (before/after removing the empties)?
0
 
Saqib Husain, SyedEngineerCommented:
Try this sorting approach

Range("A1").EntireColumn.Insert
Range("A1").Resize(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1).FormulaR1C1 = "=if(counta(rc[1]:rc" & Columns.Count & ")=0,10000000,row())"
Range(Range("A1"), Range("A1").End(xlDown)).Resize(, Columns.Count).sort Range("A1")
Range("A1").EntireColumn.Delete

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now