CODEWITHSUNDEEP
vbaexcel
Ben
Ben

Reputation: 83

VBA can't seem to detect my cell range

I have a table with data from Column A to G. After inserting 2 new columns in "A:B", I want to copy paste the values from Column I (which was initially in Column G) to Column A. But something seems wrong with my "Range(lrow, "A")" in particular and I can't figure it out. I know this might look like an amatuer mistake but I was hoping if somebody could kindly point out where I went wrong. Thanks in advance!!

        Sub Merge()
    Dim lRow As Long

    lRow = Range("A" & Rows.Count).End(xlUp).Row

    Columns("A:B").Insert Shift:=xlToRight
    Range(lRow, "A").Value = Range(Range("I1"),Range("I1").End(xlDown)).Value
End Sub

Upvotes: 1

Views: 334

Answers (1)

cptully
cptully

Reputation: 697

In working with dynamically changing rows or columns in VBA I usually prefer to use:

Range(Cells(row,col), Cells(row,col))

This is much more reliable than trying to calculate letter-number cell designations that Excel records for you. Simply Dim row and Dim col and use these to make all of your references to cells.

This website goes into far more detail than I have: https://excelmacromastery.com/excel-vba-range-cells/

Upvotes: 2

Related Questions