New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Deleting Empty Rows using a macro

Macros, VBA, Excel Automation, etc.

Deleting Empty Rows using a macro

Postby Luigi » Wed Dec 14, 2011 7:43 pm

In my worksheet i have 2 empty rows after each entry. I tried to record a macro to delete these empty rows but i can only delete 2 at a time. here is the macro. Can you show me how to change it to apply this to the full sheet and delete all the other empty rows? thanks!

Sub DeleteTwoRows()
'
' DeleteTwoRows Macro
'

'
ActiveCell.Offset(-3, -5).Range("1:1,2:2").Select
ActiveCell.Offset(-2, -5).Range("A1").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
End Sub
  • 0

Luigi
Rookie
 
Posts: 4
Joined: Dec 12, 2011
Reputation: 0

Re: Deleting Empty Rows using a macro

Postby charles » Wed Dec 14, 2011 8:15 pm

Hi,

Paste this to a module and test on a copy.
It should delete all blank rows. I have the code looking for the "Blanks" in column A.
It will only filter Column A for the "Blanks".

Code: Select all
Sub FilterDeleteBlankRows()
Application.ScreenUpdating = False
Dim VisRng As Range, cell As Range
Dim Myval As Integer
Dim lrow As Long
lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
    Range("A1:d1").Select
        Selection.AutoFilter
        With Selection
            .AutoFilter Field:=1, Criteria1:=""  '' this set the filtered data for the value
        End With
            With Worksheets("Sheet1").AutoFilter.Range

            Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
                    .Cells.SpecialCells(xlCellTypeVisible)
            '' make sure you have more than 1 row  ''
            Myval = .Range("a2:a" & lrow).SpecialCells(xlCellTypeVisible).Count
            If Myval >= "2" Then
                Range(Cells(VisRng.Offset(1, 0).Row, 1), Cells(Range("A65536").End(xlUp).Row, 4)).EntireRow.Delete
            End If
            End With
    Selection.AutoFilter
End Sub
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Deleting Empty Rows using a macro

Postby Luigi » Thu Dec 15, 2011 7:59 am

Thank you, Charles! Sorry about the "code". I was trying to educate myself about it on the FAQ page and it sent me to find the "guide" on the post page. Can you tell me where the guide is, I cannot seem to find it. Or perhaps someone could explain how to use the "code". I want to follow the rules but am quite new at this. Thanks for your patience!
  • 0

Luigi
Rookie
 
Posts: 4
Joined: Dec 12, 2011
Reputation: 0

Re: Deleting Empty Rows using a macro

Postby charles » Fri Dec 16, 2011 1:09 pm

Luigi,

The previous code I posted may not work completely. It depends on the column to the left of column A.
So here's a simpler code that may work better for you. It to only looks at column A for blank rows. It will start at the bottom of column A and work its way up to row 2 (I assume row 2 has a header).
Copy this to a module.
I also attached a copy of the workbook for your info.



Code: Select all
Sub delete_row()
Dim lrow As Long
Dim i As Long
lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row ''get last row in column A
For i = lrow To 2 Step -1
    If Cells(i, 1).Text = "" Then
        Cells(i, 1).EntireRow.Delete
    End If
Next
End Sub
  • 0

You do not have the required permissions to view the files attached to this post.
There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011

Re: Deleting Empty Rows using a macro

Postby Don » Sat Dec 17, 2011 11:14 pm

Hey Luigi,

To use the CODE tags, when you are making your post, look to the buttons directly above where you type information for the post. One of the buttons says "Code." When you want to enter any code, just click that button and insert the code in between the Code tags that appear in the text box.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 43 guests