Solved: range question

[vzachin]

hi,

i am trying to set a range in column D5. if there are 2 sets of data, then i need to set the range for the 2nd set of data in the column. The 2nd set of data is preceded by a blank cell in the column.
i have something like this which seems to work but there must be a better way.
[vba]
Sub test4()
FirstRow = range("D4").End(xlDown).Row + 2
LastRow = range("D4").End(xlDown).Row
MoreData = range("D4").End(xlDown).Offset(2, 0)
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
If MoreData <> "" Then
For i = FirstRow To LastRow
Dkey = range("D" & i)
'do stuff
Next
Else
For i = 5 To LastRow
Dkey = range("D" & i)
'do stuff
Next
End If
End Sub
[/vba]

thanks
zach

[Simon Lloyd]

This will find your range after the blank:
[vba]
Dim R1 As String, R2 As String
R1 = Range("D" & Rows.Count).End(xlUp).Address
R2 = Cells.Find(What:="", After:=Range("D4"), LookIn:=xlValues, SearchOrder:= _
xlByColumns).offset(1,0).Address
Range(R2 & ":" & R1).Select
[/vba]

[david000]

[vba]
Sub Dude()
Dim i As Integer
Dim rng As Range
Dim wk As Worksheet
Set wk = ActiveSheet
With wk
Set rng = Range("d5")
Set rng = Union(rng, rng.SpecialCells(xlCellTypeConstants))
rng.Select

MsgBox rng.Areas(1).Address & vbNewLine & rng.Areas(2).Address

End With
End Sub
[/vba]


Nice!!!

Simon Lloyd

Dim R1 As String, R2 As String R1 = Range("D" & Rows.Count).End(xlUp).Address R2 = Cells.Find(What:="", After:=Range("D4"), LookIn:=xlValues, SearchOrder:= _ xlByColumns).offset(1,0).Address Range(R2 & ":" & R1).Select

[JimmyTheHand]

This will find your range after the blank:

Simon,
your solution expects a 2nd set of data, which is a possibility, but not sure to exist.

Vzachin,
assuming that

• the 2nd set of data (if any) is at least 2 cells high
• the 1st set of data begins in row 5

this will do:

[vba] Sub test5()
Dim BottomCell As Range, TopCellRow As Long, cel As Range
Set BottomCell = Range("D" & Rows.Count).End(xlUp)
TopCellRow = Application.WorksheetFunction.Max(5, BottomCell.End(xlUp).Row)
For Each cel In Range(BottomCell, Range("D" & TopCellRow))
'do stuff
Next
End Sub[/vba]
Jimmy

[david000]

Actually, jimmythehand your code is very robust! Just forget the max and anything goes without an error.

[VBA]
Sub test6()
Dim BottomCell As Range, TopCellRow As Long, cel As Range
Set BottomCell = Range("D" & Rows.Count).End(xlUp)
TopCellRow = BottomCell.End(xlUp).Row

MsgBox Range(BottomCell, Range("D" & TopCellRow)).Address

' For Each cel In Range(BottomCell, Range("D" & TopCellRow))
' 'do stuff
' Next
End Sub
[/VBA]

[JimmyTheHand]

Just forget the max and anything goes without an error.

Max is the key to decide whether or not there is a 2nd set of data in column D. Your code works wrong, if there's no 2nd set, because it includes cell D4 which is, actually, not data but a header.
What's wrong with Max, by the way? What error do you get?

Jimmy

[vzachin]

hi jimmy,

thanks for the coding. this should work for me.

the 2nd set of data (if any) is at least 2 cells high

just wondering if there is a workaround if it's only 1 cell high?

i had come up with this late yesterday
[vba]
MoreData = range("D4").End(xlDown).Offset(2, 0)
If MoreData <> "" Then
FirstRow = range("D4").End(xlDown).Row + 2
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Else
FirstRow = 5
LastRow = range("D4").End(xlDown).Row
End If
For i = FirstRow To LastRow
Dkey = range("D" & i)
'do stuff
Next
[/vba]

and thanks to simon & david for looking at this.

thanks again,
zach

[JimmyTheHand]

just wondering if there is a workaround if it's only 1 cell high?

There is, of course, but I don't think you need it, becasue your own code is a fine piece, and should work great.
The advantage of my code was that it was short, nice and simple , but with the workaround modification it would lose all these features.

Jimmy

[david000]

What's wrong with Max, by the way? What error do you get?

Jimmy - I was mistaken I an erronous result in excel 2007.

I need to slowly back away from my haunted computer now...

I predicted the OP's next question and did get the result once without the max, but never again. (a single cell as the second data area) .

In any event, I needed the same code for my work too. So, I was over thinking the problem and don't have the skills to fix it.