VBA Clear Formatting in all columns AFTER first blank column

[powercell99]

Morning,

I am trying to write the code to clear all formatting in all columns after the first blank column in the current active worksheet.

Example: Current worksheet has text in columns A:K, but the formatting continues beyond K, and I'd like to clear the formatting in all columns starting in column L. But the next cycle there may only be text in columns A:F, so then I'd like to clear all formatting in all columns starting in column G.
I know xlToRight is involved, but when i use it, it also clears the last column that contains text. Maybe xlToLeft would work??? Hellllppppp please.

Thanks in advance

 

[AlphaFrog]

Use xlToRight but .Offset( ,1) or add 1 to the result column number so it returns the empty column next to the Last used column.

 

[Joe4]

Welcome to the Board!

This code should do it:

Sub ClearUnusedColumnFormats()


    Dim LastCell As Range
    Dim LastColumn As Integer


'   Find last used column
    Set LastCell = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    LastColumn = LastCell.Column
    
'   Clear formatting for all columns after last column
    Range(Columns(LastColumn + 1), Columns(Columns.Count)).ClearFormats
        
End Sub

 

[Joe4]

I know xlToRight is involved, but when i use it, it also clears the last column that contains text.

Not necessarily. That is only true if you know which row contains the last column with data. If your rows can be of variable, how do you know which row to look at?

The code I posted will work, regardless of that fact, as it is not dependent upon you supplying the row to look at ahead of time (it is more dynamic and figures it out itself).

 

[powercell99]

Not necessarily. That is only true if you know which row contains the last column with data. If your rows can be of variable, how do you know which row to look at?

The code I posted will work, regardless of that fact, as it is not dependent upon you supplying the row to look at ahead of time (it is more dynamic and figures it out itself).

THANKS JOE! It works perfectly. I could figure out how to get to the last column with data, but not how to get to 1 column beyond that. You Da Man! I appreciate your very quick response.

Thanks

 

[Joe4]

I could figure out how to get to the last column with data, but not how to get to 1 column beyond that.

Your welcome.

As AlphaFrog was alluding to, if you know which row you can look at the find the last column, you can use your methodology, you just need to move over (offset) one column, as you are finding the last populated column, not the first unpopulated column when using xlToRight.

So, if you knew you could always use row 1 to find the last column, you could use this could to find the first unpopulated column, i.e.
Range("A1").End(xlToRight).Offset(0,1).Column

 

[powercell99]

Your welcome.

As AlphaFrog was alluding to, if you know which row you can look at the find the last column, you can use your methodology, you just need to move over (offset) one column, as you are finding the last populated column, not the first unpopulated column when using xlToRight.

So, if you knew you could always use row 1 to find the last column, you could use this could to find the first unpopulated column, i.e.
Range("A1").End(xlToRight).Offset(0,1).Column

Awesome, great information and thanks for the instructions. I really like the forum here.