Deleting Rows with empty cells in a specific column

06-14-2009, 12:32 PM

Frankie80

Newcomer

Join Date: Jun 2009

Posts: 3

no luck

Edit by Moderator: Re http://www.xtremevbtalk.com/showthread.php?t=130452

I tried to use this code in an excel 2007 macro but it didn't like the "set" in the third line and gave me an error message " invalid outside procedure "

I would certainly appreciate any help for the clueless rookie

Last edited by Colin Legg; 06-14-2009 at 04:01 PM.

06-14-2009, 02:28 PM

HaHoBe

Centurion

Join Date: Sep 2005

Posts: 176

Hi, Frankie80,

did you copy all the code and do you have a worksheet called Sheet1 in your workbook?

Ciao,
Holger

Attached Files

XTreme130452.zip (13.5 KB, 1 views)

06-14-2009, 03:08 PM

Frankie80

Newcomer

Join Date: Jun 2009

Posts: 3

Thanks Holger,

I used all the code in post #4
I changed the name to "All Days" which is the name of the sheet I'm trying to automate
I also changed 6 to 9 as my column I'm trying to filter on is the "i" column
Still no luck same error message

this is what I tried to run

Dim CheckRange As Range, DeleteRange As Range

Set CheckRange = Worksheets("All Days").Columns(9)
CheckRange.AutoFilter Field:=1, Criteria1:="="

Set DeleteRange = CheckRange.SpecialCells(xlCellTypeVisible).EntireRow
DeleteRange.Delete

06-14-2009, 09:53 PM

HaHoBe

Centurion

Join Date: Sep 2005

Posts: 176

Hi, Frankie80,

sorry, I don´t see the reason for the error (works fine in my example) - what about the use of a modified code:

Code:

Sub DeleteEmptyCells2()

Const cstrSheet As String = "All Days"
Const clngColumn As Long = "9"

On Error Resume Next
Worksheets(cstrSheet).UsedRange.Columns(clngColumn).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

If Err <> 0 Then
  MsgBox "Error: " & Err.Number & vbCrLf & "Desprition: " & Err.Description
End If

End Sub

If the error still shows up could you please show all the code used in the module and indicate where it is located as maybe the error occurs from putting the code segment outside of a procedure or function?

Ciao,
Holger

06-15-2009, 10:53 PM

Frankie80

Newcomer

Join Date: Jun 2009

Posts: 3

Holger - Thanks again

Well clueless me has just been opening the editor and pasting that cut with nothing before or after it. Might be my problem. Tomorrow I hope to get time to work on the project. I appreciate your help for sure and wish you a good day

Paul