Blank Columns

[Rakesh]

Hi Friends,

How to delete the blank columns in all worksheets in an Excel, and how to delete the periods which occurs in the 1st ("A") column using coding.

Attached file for your reference.

Regards,
Rakesh

[Bob Phillips]

Do a Find and Replace on column A, but be sure to copy from one of the cells as they are ellipsis (...) not dots

[Bob Phillips]

To delete the blank cols

[vba]

Public Sub ProcessData()
Dim Lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastcol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

For i = Lastcol To 2 Step -1

If .Cells(3, i).Value = "" Then

.Columns(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub[/vba]

[Rakesh]

Hi James,

Thanks for your kind help. Its working fine. But it Deletes only in the Active sheet. How to Delete in all Sheets.

Thanks,
Rakesh

[mdmackillop]

[VBA]Dim sh as worksheet
for each sh in sheets
With sh
LastCol ' etc.[/VBA]

[Bob Phillips]

Probably best to use

[vba]

For Each sh In Activeworkbook.Worksheets
[/vba]

[Rakesh]

Hi James,

Where to insert this line?

For Each sh In Activeworkbook.Worksheet

Thanks,
Rakesh

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim sh As Worksheet
Dim Lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

For Each sh In Activeworkbook.Worksheets

With ActiveSheet

Lastcol = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column

For i = Lastcol To 2 Step -1

If .Cells(3, i).Value = "" Then

.Columns(i).Delete
End If
Next i
End With
Next sh

Application.ScreenUpdating = True
End Sub [/vba]