Solved

Count rows and delete blank rows

Posted on 2014-01-30
3
331 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

751 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