Selecting Blank Columns

[Carly]

I have a spreadsheet which consists of some columns having data and some not. The way it is formatting is:

Columns A & B are used then there is a blank column and then Columns D & E are used and there is a blank column & so on, upto column K.

What I would like to do is select all blank columns and set the columnwidth. Is there any way I can do this?

Regards

Carly

 

[gjadcock]

I am a little new to this stuff but, you can create a macro that has the following lines:

Range("C:C,F:F,I:I,L:L,O:O").Select
Selection.ColumnWidth = 5

This is OK if you know the column names and its not liable to change else you must check to see if the column is empty I can give you the code for this if you need it

 

[Andrew Poulsom]

Something like this?

Sub Test()
    Dim x As Integer
    With ActiveSheet
        For x = 1 To .Columns.Count
            If WorksheetFunction.CountA(.Columns(x)) = 0 Then
                .Columns(x).EntireColumn.ColumnWidth = 1
            End If
        Next x
    End With
End Sub

 

[Carly]

Andrew,

This works just how I want it to apart from the fact, if possible, I would like it to stop when it gets to end of the data. So when it gets to column L there is no more data on the spreadsheet so instead of changing all the columns from L to IV, it would be better if it just stopped.

Is there a way this can be done?

Regards
Carly

 

[Andrew Poulsom]

Well column L is 12, so:

Sub Test() 
    Dim x As Integer 
    With ActiveSheet 
        For x = 1 To 12 
            If WorksheetFunction.CountA(.Columns(x)) = 0 Then 
                .Columns(x).EntireColumn.ColumnWidth = 1 
            End If 
        Next x 
    End With 
End Sub

 

[Carly]

Thanks that works great.

I didn't realise that those numbers represented the column numbers, but I understand now

Thanks again