How To Find Last Column while there is not every column in filled in between

[nelsontan]

Hi, I am trying to fill up the blank cells accordingly to the value in above cell, but how do I find the last columns in the worksheet that no header in every column (this is the report generated by system). Currently I am setting the last column as column "Z" but instead it is not the last column, the last column may vary due to the hierarchy level might different which means the total number of column might different as well. The below is my screenshot of the raw file and also my coding. Thanks in advance.

Option Explicit
Public wbmacrobook As Workbook
Public wbrawfile As Workbook
Public vfile As Variant

Sub GFL()

Dim lr As Long

'Set Macro file
    Set wbmacrobook = ThisWorkbook

   'Open raw file
    vfile = Application.GetOpenFilename("Excel File (*.xlsx;*csv;*xls)," & "*.xlsm*" & "*.csv*" & "*.xls*", 1, "Select Excel File", "Open", False)
 
    'If Cancel then Exit
    If TypeName(vfile) = "Boolean" Then
        Application.ScreenUpdating = True
        'Application.DisplayStatusBar = True
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        Application.DisplayAlerts = True
        Application.AskToUpdateLinks = True
        Exit Sub
    Else
        Set wbrawfile = Workbooks.Open(vfile)
        Application.AskToUpdateLinks = False
    End If
   
    Application.DisplayAlerts = False

    'Copy Raw File
    wbrawfile.Activate
    Range("A:Z").Copy
   
    wbmacrobook.Activate
    Sheets("GFL").Range("A:Z").PasteSpecial Paste:=xlPasteValues
   
    'clear selection and close raw file
    wbrawfile.Activate
    Selection.Clear
    wbrawfile.Close savechanges:=False
   
    wbmacrobook.Activate
    Application.ScreenUpdating = False
    With Sheets("GFL")
      lr = .Columns("B:Z").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        On Error Resume Next
        With .Range("B7:Z" & lr)
            .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
            .Value = .Value
        End With
        Err.Clear
    End With
   
    Application.ScreenUpdating = True

End Sub

 

[maabadi]

if you want to find it based row 5 then

LC = Cells(5, Columns.Count).End(xltoLeft).Column

 

[nelsontan]

if you want to find it based row 5 then

LC = Cells(5, Columns.Count).End(xltoLeft).Column

Thanks for the prompt reply, but I am new to vba, what to do if I want to do it in range? I've tried to update in my existing coding but it doesn't work. Sorry for not putting the code in the "code format" as I want to highlight the wording.

Dim lc As Long
lc = Cells(5, Columns.Count).End(xlToLeft).Column

'Copy Raw File
wbrawfile.Activate
Range("A1:A" & lc).Copy

wbmacrobook.Activate
Sheets("GFL").Range("A1:A" & lc).PasteSpecial Paste:=xlPasteValues

'clear selection and close raw file
wbrawfile.Activate
Selection.Clear
wbrawfile.Close savechanges:=False

wbmacrobook.Activate
Application.ScreenUpdating = False
With Sheets("GFL")
lr = .Columns("B:Z").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
On Error Resume Next
With .Range("B7", Cells(lc, lr))
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value
End With
Err.Clear
End With

 

[maabadi]

Sorry for not putting the code in the "code format" as I want to highlight the wording.

For Highlighting Use Rich tags then highlight.

I've tried to update in my existing coding but it doesn't work.

Range("A1:A" & lc).Copy

You Put Lc as Row Not Column Number, you define Column here "A" and then use Lc as Row.
Use this format:

Range(Cells(1,1), Cells(Lr, Lc)).Copy

Green Part equal A1
Blue Part Equal your last column cell at Last row, for example if your lastrow is 10 & Lc is 13 (= Column M) then it equal to M10.
Change other red part with Cells to this method.

 

[EXCEL MAX]

May this can be helpful...

Sub ExpresSelect()

    Dim a, vRng As Range
    
    Set vRng = Selection
    With vRng
       a = Cells(.Find("*", , , , 1, 2).Row, .Find("*", , , , 2, 2).Column).Address
    End With
    Range(ActiveCell.Address, Range(a)).Select

End Sub