vba help

[decadence]

Hi, if you have Range(B2:B and lastrow) or (C2:C and lastrow) as the set range for example, Can the range be changed so it starts in cell 2 to lastrow but without referencing the column name or number. e.g. Range(2, lastrow) instead of cells(2, 2) or B2:B.

 

[Anthony47]

Try

Range("2:" & lastrow)

Bye

 

[decadence]

It kind of works except that it seems to be doing the whole column instead of to the last row. Here is my code with it in

    Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, mywb As String, LR As Long, LRow As Long, c As Range, x As Range
    Application.ScreenUpdating = False
    mywb = "Part Description 2017.xlsx"
    Workbooks.Open Filename:="C:\Users\MyUser\Desktop\Part Description 2017.xlsx"
    ActiveWindow.Visible = False
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks("Part Description 2017.xlsx")
    Set sh1 = wb1.ActiveSheet
    Set sh2 = wb2.Sheets("Customer Prefix")
    Application.ScreenUpdating = False
    LRow = Range("A" & Rows.Count).End(xlUp).row
    LR = sh1.Cells(Rows.Count, 2).End(xlUp).row
    For Each c In Range("2:" & LRow) 
        Set x = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not x Is Nothing Then
                c.Offset(0, 1).Value = x.Offset(0, 1).Value
            End If
    Next
    Set x = Nothing
    Workbooks(mywb).Close False
    Application.ScreenUpdating = True
End Sub

What I normally use is this

    For Each c In Range("B2:B" & LRow)

 

[mikerickson]

without referencing the column name or number

Cells are in columns, so the column has to be specified somewhere.

If you want to make the column flexible, you could use something like this.

Dim c As Range
Dim colNum as Long

colNum = 2

With Sheet1
    For Each c In Range(.Cells(2, colNum), .Cells(.Rows.Count, colNum).End(xlup))
        ' code
    Next c
End With

 

[decadence]

I kind of figured that would be the case. Is it possible to set the range from row 2 to the last row in a column if a header is found because my Column location moves from time to time.

 

[mikerickson]

If you have a cell and want to select rows 2-the end of that column

With myCell.EntireColumn
    Range(.Cells(2,1), .Cells(.Rows.Count,1).End(xlUp)).Select
End With