Solved

Count rows and delete blank rows

Posted on 2014-01-30
3
319 Views
Last Modified: 2014-01-30
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
Comment
Question by:Seamus2626
  • 2
3 Comments
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 250 total points
ID: 39820486
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39820491
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 39820503
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question