Determining the last row in a block of data 1

[t16turbo]

I have several spreadsheets with the same kind of formatting.
they basically consist of a block of data (varying number of rows) from column A through to column AB

I need to use column B to detect the last row of data (as there is something slightly lower down in column A and a sections with totals starting at column L after the data.

how can I tell VBA to find the last row of data using column B, and then select every row from 1 to that row number?

 

[taupirho]

Try this for size:

Range("B1").Select
x = Range(Selection, Selection.End(xlDown)).Count
Rows("1:" & x).Select

 

[PHV]

Have a look in the FAQ area of this forum: at least two ways to retrieve the last used row of a range.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

NOTE: tomreid's suggestion will fail if column B ever contains a blank cell within the data set.

Given the information you provided, I would use something like:
[COLOR=blue white]range([1:1], [b65536].end(xlup)).select[/color]

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[t16turbo]

John,

how does that work?
I can see that you are going to the last B cell.
what does the [1:1] do.

I chose column B specifically because it will never contain blanks.
i'll give yours a whirl though!

 

[AnotherHiggins]

[a1] is the same thing as range("a1")

So [1:1] selects the entire first row, just as range("1:1") would.

The breakdown goes like this:
[COLOR=blue white]range([1:1]^[red]1[/red], [b65536]^[red]2[/red].end(xlup)^[red]3[/red]).select^[red]4[/red][/color]

^[red]1[/red] Using [1:1] as the Cell1 argument selects the entire first row
^[red]2[/red]There are only 65,536 rows in a worksheet, so [B65536] is the very last cell in column B.
^[red]3[/red]This goes to the first non-blank cell in column B above [B65536]. This cell becomes the argument for Cell2
^[red]4[/red]Selects rows (1) through (the last row containing data in column B)

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[t16turbo]

cunning!!!
I like!!

while we are on the sbject, I also need to know how to do another row select.

another 'project' I am working on requires that I select every row above the occurence of "Sub total (filtered projects)" in column 'L'

do you also know how to do that????

 

[AnotherHiggins]

Using your Macro Recorder, you can see all of the arguments for FIND, but it would go something like this:

Range([1:1], Columns("L:L").Find(What:="Sub Total").Offset(-1)).Select

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[t16turbo]

blimey! you're good!!