PDA

View Full Version : Solved: range question



vzachin
05-27-2008, 06:45 PM
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.

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


thanks
zach

Simon Lloyd
05-27-2008, 07:00 PM
This will find your range after the blank:

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

david000
05-27-2008, 09:52 PM
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



:clap: 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
05-27-2008, 10:52 PM
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 5this will do:

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
Jimmy

david000
05-27-2008, 11:28 PM
Actually, jimmythehand your code is very robust! Just forget the max and anything goes without an error.


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

JimmyTheHand
05-28-2008, 12:33 AM
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
05-28-2008, 03:53 AM
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

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


and thanks to simon & david for looking at this.

thanks again,
zach

JimmyTheHand
05-28-2008, 04:48 AM
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. :thumb
The advantage of my code was that it was short, nice and simple :cool:, but with the workaround modification it would lose all these features.

Jimmy

david000
05-28-2008, 08:18 AM
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...:bug:

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.