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

Question

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

What column are you trying to get the last row for? You have C5 there, if you get rid of the 'C' it will test column E for you and store that last row. — Captain Grumpy
– Captain Grumpy, Commented Sep 19, 2016 at 23:12

Comintern · Accepted Answer · 2016-09-19 23:12:59Z

4

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

answered Sep 19, 2016 at 23:12

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related