Incorrect Last Row Assigned In Data Set Excel

Question

I'm attempting to set a last row for dynamic iteration through a data set and am encountering a bug i've never experienced before. lastRow is being set to the row before the start of the data for some reason. If the data starts in row 21 it would set lastRow to 20. brokerLocationInTable is a range object passed into the function that denotes the location of the broker's name in the data set.

Code Causing error

lastRow = CoverageAndRateSheet.Range("E" & brokerLocationInTable.Row).CurrentRegion.Rows.Count

The data is stored in a slightly odd fashion. The broker name is in column D and immediately adjacent to the name is the list of clients in column E that corresponds to that broker. Once that broker's client list is exhausted there's a blank row and the next broker with his/her corresponding client list begins, which is why i'm using the "control shift down" method rather than iterating upwards from the bottom of the sheet.

You only seem to be returning the number of rows in that region, rather than the last row in that region. For example, if your broker starts on row 30, and there are ten rows so that the last row is on row 39, the `lastRow` variable will still only be 10 because your code is only returning the number of rows. You'll need it to get the last row with a `.End(xlDown).Row` if you want to use the "control shift down" method. — tigeravatar, Aug 26 '16 at 13:49
@tigeravatar bingo. That was it. Should have realized .count would have returned a row count rather than an actual actionable row number. Thanks for the help! Post an answer and i'll select it. — StormsEdge, Aug 26 '16 at 13:52
Possible duplicate of [Error in finding last used cell in VBA](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Mathieu Guindon, Aug 26 '16 at 14:05

score 0 · Answer 1 · answered Aug 26 '16 at 13:57

use

With CoverageAndRateSheet.Range("E" & brokerLocationInTable.row)
    lastRow = .CurrentRegion.Rows(.CurrentRegion.Rows.Count).row
End With

because

myRange.CurrentRegion.Rows.Count

just returns the number of rows of myRange current region
myRange.CurrentRegion.Rows(.CurrentRegion.Rows.Count)

returns the last row of myRange current region
myRange.CurrentRegion.Rows(.CurrentRegion.Rows.Count).Row

returns the row index of the last row of myRange current region

@StormsEdge, did you try this? – user3598756 Aug 29 '16 at 14:14 — user3598756, Aug 29 '16 at 14:14

Incorrect Last Row Assigned In Data Set Excel

1 Answers1