Delete Blank Columns from a specified Range

[Jasvindra]

Hi

I am using the below code to delete blank columns in a particular range of Columns but code is not working. Not even showing any Error

Sub DeleteBlankColumns()


Dim MR As Range
Dim iCounter As Long



Set MR = Range("V1:AI709")



For iCounter = MR.Columns.Count To 1 Step -1



If Application.CountA(Columns(iCounter).EntireColumn) = 1 Then
Columns(iCounter).Delete
End If


Next iCounter
End Sub

 

[Fluff]

If you only want to check cols V:AI try

Sub DeleteBlankColumns()

   Dim iCounter As Long

   For iCounter = 35 To 22 Step -1
      If Application.CountA(Columns(iCounter).EntireColumn) = 1 Then
         Columns(iCounter).delete
      End If
   Next iCounter
End Sub

 

[Jasvindra]

Great It worked
But i wanted to know if we can assign Range via SET Method

 

[Marcelo Branco]

Or

Sub DeleteBlankColumns()
    Dim MR As Range
    Dim iCounter As Long
    
    Set MR = Range("V1:AI709")
    For iCounter = MR.Columns.Count To 1 Step -1
        If Application.CountA([COLOR=#ff0000]MR[/COLOR].Columns(iCounter).EntireColumn) = 1 Then
            [COLOR=#ff0000]MR[/COLOR].Columns(iCounter).Delete
        End If
    Next iCounter
End Sub

 

[Jasvindra]

Thanks Marcelo.