Solved

Count rows and delete blank rows

Posted on 2014-01-30
3
313 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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 …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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