PDA

View Full Version : Blank Columns



Rakesh
03-20-2011, 02:59 AM
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

xld
03-20-2011, 04:56 AM
Do a Find and Replace on column A, but be sure to copy from one of the cells as they are ellipsis (...) not dots

xld
03-20-2011, 05:00 AM
To delete the blank cols



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

Rakesh
03-20-2011, 06:53 AM
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
03-20-2011, 11:08 AM
Dim sh as worksheet
for each sh in sheets
With sh
LastCol ' etc.

xld
03-20-2011, 03:53 PM
Probably best to use



For Each sh In Activeworkbook.Worksheets

Rakesh
03-21-2011, 03:21 AM
Hi James,

Where to insert this line?

For Each sh In Activeworkbook.Worksheet

Thanks,
Rakesh

xld
03-21-2011, 06:11 AM
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