Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range....
Once worked fine, but now error on this line....
Set sht = Sheets(x) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range....
will actually be for each sheet after sheet "apples".
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
subscript out of range....
thanx much!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Programming | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of Range | Excel Programming | |||
Subscript out of Range Q | Excel Programming | |||
Subscript out of range | Excel Programming |