Find last column with value ignoring formulas

[Krusty1]

Hi all,

Have learnt a lot from these forums, now my first time asking for help....

In the spreadsheet I'm building, I want to be able to find the last column of data and copy-paste values/formats overwriting existing formulas in that specific column on the Gap Tracker tab so the source data on the Gap Data tab can be replaced in the following week to fill in the next column.

The columns start at O and finish at AT on the Gap Tracker tab.

This is one of the more successful codes I have tried thus far however, it still responds to formulas even when 0 values are written as "".
I want the code to find the numerical value including 0, not the ongoing cells that are prepopulated with formulas.

Any help would be sincerely appreciated.
Thank you.

Sub FindLastColumn()

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Gap Tracker")

  LastColumn = sht.Range("A2").End(xlToRight).Column
  sht.Columns(LastColumn).Copy
  sht.Columns(LastColumn).PasteSpecial Paste:=xlPasteValues
  sht.Columns(LastColumn).PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
End Sub

 

[DanteAmor]

Welcome to the board!

Try this

Sub FindLastColumn()
  Dim sht As Worksheet
  Dim LastColumn As Long, i As Long
  
  Set sht = ThisWorkbook.Worksheets("Gap Tracker")
  For i = sht.Range("A2").End(xlToRight).Column To 1 Step -1
    If sht.Cells(2, i).Value <> "" Then
      LastColumn = i
      Exit For
    End If
  Next
  If LastColumn = 0 Then LastColumn = 1
  sht.Columns(LastColumn).Copy
  sht.Columns(LastColumn).PasteSpecial Paste:=xlPasteValues
  sht.Columns(LastColumn).PasteSpecial Paste:=xlPasteFormats
  Application.CutCopyMode = False
End Sub

 

[Krusty1]

That worked a treat DanteAmor.

Thank you so much..

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.