subscript out of range....

[J.W. Aldridge]

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

 

[J.W. Aldridge]

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

Set sht = Sheets(x)

 

[JLGWhiz]

Try quote marks around x ... "x", without quotes VBA thinks it is a
variable. If it is a variable, it is not declared.

 

[Ryan H]

What is your x variable equal too? The reason you are getting the error is
because VBA can't find that sheet index. Worksheets are indexed as well as
named. You can refer to a sheet using the index number (in your case, x) or
its name. For example, when you open a new workbook in Excel you have 3
sheets in it, thus:

Sheets(1) = Sheets("Sheet1")
Sheets(2) = Sheets("Sheet2")
Sheets(3) = Sheets("Sheet3")

Try this code. I made a few recommendations that may help you in the future
as well.

1. I noticed you had some undeclared variables (x and sht). I would always
declare your variables.

2. I would ALWAYS use Option Explicit at the top of your modules. This
will identify any issues before your code is run.

3. It may be easier to use Range("AB:AB").Column, instead of column number
28, because column numbers are kinda hard to figure out. For example, what
is column number 17? Answer: Its much easier to read your code when you use
Range("Q:Q").Column.

4.) In this example of code I would use the sheet name instead of x. Just
put the sheet name in where I have Sheets("Sheet Name Here")


Hope this helps! If so, let me know, click "YES" below.



Option Explicit

Sub AAA()

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

Set sht = Sheets("Sheet Name Here")
StartCol = Range("A:A").Column ' column A
EndCol = Range("AB:AB").Column ' 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

 

[J.W. Aldridge]

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

 

[Ryan H]

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

 

[J.W. Aldridge]

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

 

[Ryan H]

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

 

[J.W. Aldridge]

thanx much!