• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Deleting the Columns should change the variables:

Abhijeet R. Joshi

Abhijeet R. Joshi

Active Member
Hi all,

Can you please help with the following issue that I have recently encountered numerous times..

Here is what happens, I define the Columns as variable and then delete some columns if the criteria is not followed...
below is the code I have used:

Code: 
Sub Macro1()
'
    Range("IV1").Select
    Selection.End(xlToLeft).Select
    dcc = ActiveCell.Column
    For i = dcc To 1
        If Cells(1, i).Value <> "" Then
        Columns(i).Delete
        Else
        End If
    Next i
End Sub

Hope someone has an answer..:)
 
Need to "step" backwards. By default, the For...to loop increments by 1. Try this:
Code: 
Sub Macro1()
Dim dcc As Long, i As Long
    dcc = Range("IV1").End(xlToLeft).Column
    
    For i = dcc To 1 Step -1
        If Cells(1, i).Value <> "" Then
            Columns(i).Delete
        End If
    Next i
End Sub
 
If those are text headings then you can avoid loop as well.
Code: 
Sub Macro1()
Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft)).SpecialCells(xlCellTypeConstants, 2).EntireColumn.Delete
End Sub
 
  • Like
Reactions: Hui
Thanks Luke and Shri for your replies...those are really helpful...

@Shri: Can you help me understand the below in your code??

Code: 
xlCellTypeConstants
 
Hi Abhi,

In XL, if you press Ctrl+g, it will bring up the Goto dialogue. Click on the "Special Cells" button. This now shows you all the special classifications that XL can look for, which is what is used in the code. The xlCellTypeConstants is the same as clicking the "Constants" button in the dialogue you see. A constant in this case refers to any cell that contains a static value, ie not a formula. The ", 2" bit at the end indicates that the type of constant we are looking for is a Text string.
 
Hi Luke,

On similar lines I tried to execute the below code...
Code: 
Sub Macro1()
Application.DisplayAlerts = False
For sh = 1 To ActiveWorkbook.Sheets.Count Step -1
    If Sheets(sh).Name <> "Main" Then
        Sheets(sh).Select
        Sheets(sh).Delete
    Else
    End If
Next sh
Application.DisplayAlerts = True
End Sub

However when the code hits the below line it directly to pushes me off the for next...and ends the sub..:(

Code: 
For sh = 1 To ActiveWorkbook.Sheets.Count Step -1
 
You must log in or register to reply here.
Back
Top