2

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?

Sean Reeves
  • 27
  • 1
  • 4
  • Not sure what you mean but maybe [this](https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba) helps or more in detail [here](http://www.cpearson.com/excel/ArraysAndRanges.aspx) – Storax May 27 '18 at 14:43
  • What is “chkcell”? A Range? If so, what is “Left(chkCell, 1)” supposed to do? – DisplayName May 27 '18 at 14:57
  • @DisplayName I'm guessing the OP is using `chkCell` to manually enter the address. The syntax would indicate it's grabbing the first letter for a column address. `Left(chkCell.Value,1)` – pgSystemTester May 27 '18 at 15:21
  • @PGCodeRider you're correct. – Sean Reeves May 28 '18 at 21:38

2 Answers2

2

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.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
0
dim rng as range
set rng = thisworkbook.sheets("sheet1").range("a1:a666")
Doug Coats
  • 6,255
  • 9
  • 27
  • 49