The current code is as follows:
With Sheets(sheetChk)
a = .Range(chkCell, .Range(Left(chkCell, 1) & Rows.Count).End(xlUp)).Value
End With
sheetChk is defined as the desired sheet index. chkCell is defined as the desired "first cell" in the range.
I am attempting to assign a range to a. The problem is that the above code grabs the last cell in the column, entirely. Rather, I want to end at a specific row as I have other data in the same column after the desired range that needs to be ignored. How can I accomplish this?
First to assign a range, you need to have a Set first.
Example: Set a = Range("A1")
Another problem I see is that you're putting a .value at the end. That doesn't make sense for a range.
If you want to specify a specific row, then you need to include that in the code instead of using end(xlUp).
Example if it's row 50:
With Sheets(sheetChk)
Set a = .Range(chkCell, .Range(Left(chkCell, 1) & 50).End(xlUp))
End With
What you're currently using is finding the bottom row being used, which doesn't sound like you want. If you want to go the other direction (i.e. from the starting cell down until there's an empty cell), you can use this code:
With Sheets(sheetChk)
Set a = .Range(chkCell, .Range(Left(chkCell, 1) & chkCell.Row).End(xlDown))
End With
Based on your code, it looks like you might be putting an address in whatever cell chkCell is. If you have the row in that cell, and assuming you never exceed column z, then you could use this code to find the row:
With Sheets(sheetChk)
Set a = .Range(chkCell, .Range(Left(chkCell, 1) & Right(chkCell,Len(chkCell)-1))
End With
If that doesn't work, you need to figure out some method determine what row to use. Hope that helps.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments