Range Question

[ummjay]

hi! I have a sheet which contains content in cells A1:H3, row 4 & 5 is blank, and row 6 starts a new table A6:H9.

I was trying to declare a range for A1:H last. In the event the top table 1 row, or 2, or 3, I wanted to have the range select the last row. doesnt appear to be working properly:

Header	Header	Header	Header	Header	Header	Header	Header
x	x	x	x	x	x	x	x
x	x	x	x	x	x	x	x
Header	Header			Header	Header		Header
x	x	x	x	x	x		x
x	x	x	x	x	x		x
x	x	x	x	x	x		x

Set rng = Range("A1", Range("H" & Rows.Count).End(xlUp))

how do i get it to stop in between the two table? Seems it grabs both, and ends at the end of the last table.

 

[Joe4]

As long as your table has at least one row of data in it, and every line with data has someething in column A, you can use:

Dim lr as Long
lr = Range("A1").End(xlDown).Row
Set rng = Range("A1:H" & lr)

 

[Bosquedeguate]

Are your ranges always 3 rows or can it vary?

 

[Herakles]

hi! I have a sheet which contains content in cells A1:H3, row 4 & 5 is blank, and row 6 starts a new table A6:H9.

I was trying to declare a range for A1:H last. In the event the top table 1 row, or 2, or 3, I wanted to have the range select the last row. doesnt appear to be working properly:

Header	Header	Header	Header	Header	Header	Header	Header
x	x	x	x	x	x	x	x
x	x	x	x	x	x	x	x
Header	Header			Header	Header		Header
x	x	x	x	x	x		x
x	x	x	x	x	x		x
x	x	x	x	x	x		x

Set rng = Range("A1", Range("H" & Rows.Count).End(xlUp))

how do i get it to stop in between the two table? Seems it grabs both, and ends at the end of the last table.

Private Sub subGetRange()
Dim rng As Range

    Set rng = ActiveSheet.Range("A1").CurrentRegion

    ' This will set the range to the first block of data.

End Sub

 

[Joe4]

Private Sub subGetRange()
Dim rng As Range

    Set rng = ActiveSheet.Range("A1").CurrentRegion

    ' This will set the range to the first block of data.

End Sub

Oh yeah, forgot about CurrentRegion!
That is probably an easier and more foolproof option, as long as you have at least one completely blank row between the tables!

 

[ummjay]

current region worked! how do i get it to ignore columns I and J? I don't want that included in the range.

 

[Herakles]

Project creep!!

We didn't know that you had more columns.

Private Sub subGetRange()
Dim rng As Range

    With ActiveSheet.Range("A1").CurrentRegion
        Set rng = .Resize(.Rows.Count, 8)
    End With
    
End Sub

 

[Joe4]

current region worked! how do i get it to ignore columns I and J? I don't want that included in the range.

Just amend it to:

Set rng = Intersect(ActiveSheet.Range("A1").CurrentRegion, Range("A:H"))

 

[ummjay]

thank you both!

 

[Herakles]

thank you both!

Are you going to want to set a range reference for each block of data?