subscript out of range....
 

Sub AAA()
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long
Set sht = Sheets(x)
StartCol = 1 ' column A
EndCol = 28 ' column AB
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(sht.Columns(ColNdx)) = 1 Then
sht.Columns(ColNdx).Delete
End If
Next ColNdx
End Sub

subscript out of range....
 

Once worked fine, but now error on this line....

Set sht = Sheets(x)

subscript out of range....
 

There are two different ways that would be best, but it depends on your
application. If you want to run this code for "ALL" sheets in the workbook
use code 1. If you are only wanting to run the code on particular sheets in
the workbook use code 2. Code 2 will allow you to build a collection of
sheets, just name the sheets you want the code to run on like I have. Hope
this helps! If so, let me know, click "YES" below.

Code 1:

Sub AAA()

Dim sht As Worksheet
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long

StartCol = Range("A:A").Column ' column A
EndCol = Range("AB:AB").Column ' column AB

For Each sht In Worksheets
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(sht.Columns(ColNdx)) = 1 Then
sht.Columns(ColNdx).Delete
End If
Next ColNdx
Next sht

End Sub


Code 2:

Sub AAA()

Dim colMySheets As Collection
Dim sht As Worksheet
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long

Set colMySheets = New Collection
With colMySheets
.Add Sheets("Sheet1")
.Add Sheets("Sheet2")
.Add Sheets("Sheet3")
'etc.
End With

StartCol = Range("A:A").Column ' column A
EndCol = Range("AB:AB").Column ' column AB

For Each sht In colMySheets
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(sht.Columns(ColNdx)) = 1 Then
sht.Columns(ColNdx).Delete
End If
Next ColNdx
Next sht

End Sub

--
Cheers,
Ryan


"J.W. Aldridge" wrote:

Thanx all.

Ryan,
I actually will be running this on several sheets, or rather different
ones.
the sheet name will change. So, would it be ideal to somehow put "with
this activesheet" in here somewhere?
If so, where and how please?

Thanx
.

subscript out of range....
 

will actually be for each sheet after sheet "apples".

subscript out of range....
 

In that case, use this. Hope this helps! If so, let me know, click "YES"
below.

Sub AAA()

Dim sht As Long
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long

StartCol = Range("A:A").Column ' column A
EndCol = Range("AB:AB").Column ' column AB

For sht = Sheets("apples").Index + 1 To Sheets.Count
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(Sheets(sht).Columns(ColNdx)) = 1 Then
Sheets(sht).Columns(ColNdx).Delete
End If
Next ColNdx
Next sht

End Sub
--
Cheers,
Ryan


"J.W. Aldridge" wrote:

will actually be for each sheet after sheet "apples".
.

subscript out of range....
 

thanx much!