Deleting empty columns

[rabbits]

I'm a VBA novice, and I'm trying to write a macro that deletes all the empty columns between my data. The number and location of the empty columns changes every time.

This is what I have so far from cobbling together previous advice from here and online, but when I run it nothing seems to happen.

Sub FindLastColumn()
'   Find last column used in row 9
    Dim lc As Long
    lc = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
   
'   Loop through all columns backwards
    For c = lc To 1 Step -1
        If Cells(11, c).Value = " " Then Cells(11, c).EntireColumn.Delete
    Next c
End Sub

Thanks

 

[vcoolio]

Hello Rabbits,

Assuming that the blank columns are entirely blank(all the way down your column data, inc. Row1), then this may work:-

Sub Test()

    Application.ScreenUpdating = False
    
    On Error Resume Next
        With Sheet1.Rows(1)
               .SpecialCells(4).EntireColumn.Delete
        End With
    On Error GoTo 0

    Application.ScreenUpdating = True
    
End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[rabbits]

Hello Rabbits,

Assuming that the blank columns are entirely blank(all the way down your column data, inc. Row1), then this may work:-

Sub Test()

    Application.ScreenUpdating = False
   
    On Error Resume Next
        With Sheet1.Rows(1)
               .SpecialCells(4).EntireColumn.Delete
        End With
    On Error GoTo 0

    Application.ScreenUpdating = True
   
End Sub

I hope that this helps.

Cheerio,
vcoolio.

Thanks for the suggestion, but this didn't work unfortunately - the code ran but nothing seemed to happen (no errors or anything)

Also, how would I update 'Sheet1' name in the code? I've tried doing it with and without quotation marks but only get syntax errors.

Thanks

 

[vcoolio]

Hello Rabbits,

If Sheet1 is not the correct worksheet then nothing will happen.

Not knowing your worksheet name, I used a Sheet code (Sheet1) but your actual sheet code may be different. In the VB Editor, over to the left in the Project Explorer, you'll see your worksheets listed by name in parentheses and the Sheet Codes immediately to the left (Sheet1, Sheet2 etc...). You can change the sheet code to suit or you can simply use ActiveSheet instead. You could also declare a worksheet variable and use that instead. For example:-

Dim wsR As Worksheet: Set wsR = Sheets("Rabbitts")

If you want to use the worksheet variable, place it just above:

Application.ScreenUpdating = False

I hope that this helps.

Cheerio,
vcoolio.