Referencing last column and row

[jpj31]

I've defined the last row and column as follows:
Dim lastcolumn As Long
Dim totalrow As Long

' Initialize to start at bottom of column D
totalrow = ws1.Cells(Rows.Count, "D").End(xlUp).Row

' Define last column in row 16
lastcolumn = ws1.Cells(16, Columns.Count).End(xlToLeft).Column

I'm trying to set a range from K16 through the last column and last row and filldown. Columns are being added and causing me to manually change the last column reference from my original code. How can I reference this? Below is what I tried, but it's not working.

ws1.Range("k16:" & lastcolumn & totalrow).Select

 

[Joe4]

The issue is that lastcolumn is returning a number, not a letter.
So you need to use CELLS (which allows you to use numbers for columns) instead of RANGE.

Try:

ws1.Range(ws1.Cells(16, 11), ws1.Cells(totalrow, lastcolumn)).Select

 

[n8te]

I personnally don't like hardcoding cells in VBA code. Although I'm no expert, I think it's best to fully proceed with automated function capable of finding these coordinates.
This being said, I'd go with the address function. such as

Dim lastcolumn As Long, totalrow As Long, startcel as string, endcel as string
totalrow = ws1.Cells(Rows.Count, "D").End(xlUp).Row
lastcolumn = ws1.Cells(16, Columns.Count).End(xlToLeft).Column
startcel="K16"

endcel= ws.Cells(totalrow,lastcolumn).address

ws.1.Range(startcel & ":" & address").Select

 

[n8te]

I personnally don't like hardcoding cells in VBA code. Although I'm no expert, I think it's best to fully proceed with automated function capable of finding these coordinates.
This being said, I'd go with the address function. such as

Dim lastcolumn As Long, totalrow As Long, startcel as string, endcel as string
totalrow = ws1.Cells(Rows.Count, "D").End(xlUp).Row
lastcolumn = ws1.Cells(16, Columns.Count).End(xlToLeft).Column
startcel="K16"

endcel= ws.Cells(totalrow,lastcolumn).address

ws.1.Range(startcel & ":" & address").Select

I made a mistake in the last line: ws.1.Range(startcel & ":" & endcel).Select

 

[jpj31]

I personnally don't like hardcoding cells in VBA code. Although I'm no expert, I think it's best to fully proceed with automated function capable of finding these coordinates.
This being said, I'd go with the address function. such as

Dim lastcolumn As Long, totalrow As Long, startcel as string, endcel as string
totalrow = ws1.Cells(Rows.Count, "D").End(xlUp).Row
lastcolumn = ws1.Cells(16, Columns.Count).End(xlToLeft).Column
startcel="K16"

endcel= ws.Cells(totalrow,lastcolumn).address

ws.1.Range(startcel & ":" & address").Select

Yeah, the more I'm utilizing VBA the more I agree with you. Thanks for the help, I'm learning a ton!