Find last column with Data

[olympiac]

I need to find the last column with data in a spreradsheet and add 2 new columns on the right considering that the headings will start from row 3

How can I modify this code to get the result?

Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

End If
End Sub

 

[JackDanIce]

Try:

Sub FindLastColumn()

Dim LastColumn As Integer

If WorksheetFunction.CountA(Cells) > 0 Then _
  LastColumn = Cells(3,Columns.Count).End(xlToLeft).Column

End Sub

 

[olympiac]

Thanks for your answer
What about now if I want to add the heading "bal bla bla" in the new column?

 

[JackDanIce]

Try:

Sub FindLastColumn()
 
Dim LastColumn As Integer
 
Application.ScreenUpdating = False
 
If WorksheetFunction.CountA(Cells) > 0 Then _
  LastColumn = Cells(3,Columns.Count).End(xlToLeft).Column
 
Cells(3, LastColumn + 1) = "Your new heading"
Cells(3, LastColumn + 2) = "Next to your new heading"
 
Application.ScreenUpdating = True
 
End Sub

 

[olympiac]

Great! It works.

Cheers

 

[olympiac]

Last more thing.

Do you know how to enrich the code to copy the format from the last column to the new columns added?

 

[JackDanIce]

Try:

Sub FindLastColumn()
 
Dim LastColumn As Integer
 
Application.ScreenUpdating = False
 
If WorksheetFunction.CountA(Cells) > 0 Then _
  LastColumn = Cells(3, Columns.Count).End(xlToLeft).Column
 
Cells(3, LastColumn).Copy
 
With Cells(3, LastColumn + 1)
    .PasteSpecial Paste:=xlPasteFormats
    .Value = "Your new hearding"
End With
 
With Cells(3, LastColumn + 2)
    .PasteSpecial Paste:=xlPasteFormats
    .Value = "Next to your new heading"
End With
 
With Application
    .ScreenUpdating = True
    .CutCopyMode = False
End With
 
End Sub

 

[olympiac]

Genial.
Many thanks for your support

 

[JackDanIce]

You're welcome

(Genial?)

 

[olympiac]

Genial it's a french word....it means Brilliant.