Excel Macro for deleting empty cells 1

[pbcharlie]

does anyone have a macro that will delete all empty cells within a range and move the rest to the right or left?
Thanks in advance
Charlie

 

[mmandk9]

Charlie,

I have a piece of code where I filter and delete based on a number of criteria. Here are the relevant lines:

w.Columns.AutoFilter Field:=2, Criteria1:=vntWebIDFltrs(i)
w.Rows("2:" & Selection.End(xlDown).Row).Delete shift:=xlUp

where w is dimmed as a worksheet and vntWebIDFltrs(i) is an array of of criteria I have defined for filtering. You can modify it to something like this:

w.Columns.AutoFilter Field:=2, Criteria1:=Empty
w.("B2:B" & Selection.End(xlDown).Row).Delete shift:=xlToLeft

You may need to play with it a little in order to get it to work in your sub.

HTH MM

 

[pbcharlie]

I managed to write this.
Sub delete_empty_cells()
'
'
' © 21/02/2003 by Charlie Snedden
'
'
'


Dim looper
Dim looperx
For looperx = 1 To 210
For looper = 1 To 200
ActiveCell.Offset(0, 1).Select




Do While IsEmpty(ActiveCell)


ActiveCell.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -1).Select
Do While IsEmpty(ActiveCell)

ActiveCell.Offset(0, -1).Select
Loop
Loop
Next looper
Selection.End(xlToLeft).Select
ActiveCell.Offset(-1, 0).Select
Next looperx
End Sub


Thanks for you help.

Charlie Thanks in advance
Charlie