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
Bob Phillips
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
Bob Phillips
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.
Bob Phillips
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
Bob Phillips
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.