CODEWITHSUNDEEP
vbaexcel
sayth
sayth

Reputation: 7028

VBA - Last Row number breaks as reference cell empty but isn't

I have a macro which is part of a few, however this is the first to tidy up sheet before running

To determine the column and table to tidy I am trying to find the last empty value and create a column and table range to use as variable throughout my modules. However failing at an early bit whether I choose cells C4 or C5 or refer to them in R1C1 style it breaks as if cells were empty however they are not.

It breaks at

LRow = ws.Cells(Rows.Count, C5).End(xlUp).Row

Unsure how to get it to proceed.

Sub Tidy()
'
' Tidy Macro
'

'
    Dim table_1 As Long
    Dim table_2 As Long
    Dim col_len, table_len As Range
    Dim LRow As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.ActiveSheet
    LRow = ws.Cells(Rows.Count, C5).End(xlUp).Row
    Set col_len = ws.Range("C4:C" & Cells(LRow).Address(False, False))
    Set table_len = ws.Range("A4:F" & Cells(LRow).Address(False, False))
    table_2 = Worksheets("DumpSheet").Cells(Row.Count, R5C10).End(xlUp).Row

    Range("A5").Select
    ActiveCell.FormulaR1C1 = "=R1C1"
    Range("A5").Select
    Selection.AutoFill Destination:=Range("A5:A" & col_len)
    Range(table_len).Select
    Selection.Copy
    Range("H5").Select
    ActiveSheet.Paste
    Range("B5").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-4]C"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=R1C2"
    Range("B5").Select
    Selection.AutoFill Destination:=Range("B5:B" & col_len)
    Range("B5:B" & table_1).Select
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "=R1C9"
    Range("I5").Select
    Selection.AutoFill Destination:=Range("I5:I29")
    Range("I5:I" & table_2).Select
End Sub

Upvotes: 1

Views: 186

Answers (1)

Comintern
Comintern

Reputation: 22185

C5 is being treated as a variable that hasn't been assigned. It is not a cell reference. You're looking for something more like:

LRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

Upvotes: 4

Related Questions