Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Count rows and delete blank rows

Posted on 2014-01-30
3
Medium Priority
?
344 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 1000 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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

705 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