Loop code to delete all blank cells in multiple worksheets

[Lizard07]

Hi - How would I make the below code loop through multiple worksheets in a workbook. The number worksheets varies every time

Option Explicit

Sub DeleteBlanks()
Dim intCol As Integer

intCol = 8
Range(Cells(15, intCol), Cells(40, intCol)). _
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub


Thanks!

 

[VoG]

Try

Sub DeleteBlanks()
Dim intCol As Integer, ws As Worksheet

intCol = 8
For Each ws In ActiveWorkbook.Worksheets
    ws.Range(ws.Cells(15, intCol), ws.Cells(40, intCol)). _
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Next ws
End Sub

 

[Lizard07]

Thank-you, worked perfectly!

 

[madforgolf]

code to delete all blank cells in a range

Hi Folks,
I have been searching the history for something like this.


how can i adapt this for a range of cells A1:W500

dont know how to read the above intcol=8
so asking for help on this thread. sorry if i have broken a rule ref to new threads, but didnt see anythng as close as this in my searches.

Marty

 

[VoG]

No rules broken - in fact we encourage members to post follow-ups to their original thread, so well done. Try

Sub DeleteBlanks()
ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Range("A1:W50").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Next ws
End Sub

 

[madforgolf]

Hi Vog,
Thanks for the reply, this will save a lot of time.

Cheers

Marty