Find used range and ignore blank cells when running macro?

[Guest]

I am not sure how to word the subject or the question.
In the code below I am iterating thru a range to test conditions. My
problem is the range will vary all the time. It will always start in "C5"
and the rows/Coulumns will always vary.

In this code "Set rRngCol = Range("C5", Range("g" & Rows.Count).End(xlUp))
'when finds empty cell, program ends?"
I am setting a fixed range, how can I make the range a variable dependant on
any worksheet? (I have the worksheet iteration completed, just working out
the cell iterations).

Begin code===================
Sub test3()
Dim Dif As Long
Dim rRngCol As Range
Dim i As Range 'range of cells
Dim sEmpName As String 'for Employee name in column A

'i.Address is cell address. example "C5"

Set rRngCol = Range("C5", Range("g" & Rows.Count).End(xlUp)) 'when finds
empty cell, program ends?

For Each i In rRngCol

If IsDate(i) = True Then
Dif = Date - i
If Dif > "365" Then
MsgBox i & " 1+ year" & " " & i.Address & " " & sName
Else
If Dif > "302" Then
MsgBox i & " 10+ months" & " " & i.Address
Else
End If
End If
End If
NextCell:
Next i
End Sub
End code=======================

--
Regards

Rick
XP Pro
Office 2007

 

[Roger Whitehead]

For Excel <= 2003 I'd usually use:

set myrange = activesheet.usedrange

It's a little problematic - Excel remembers columns/rows where cells have
been deleted as still active. You have to delete the rows/columns, move to
A1 then Save, before Excel re-maps the used range.

 

[Guest]

Variable length ranges are a fundamental problem in Excel and there are
various ways of dealing with them. I'm not answering your problem directly
but you should be able to work something out from the following.

Select the current region:
Range("A1").CurrentRegion.Select (you'd probably use C5!)

Then use Offset and/or Resize to move the cursor to the cell or relative
range you're interested in:

e.g. (to select next blank cell under a list)
Range("A1").CurrentRegion.Select
Selection.Offset(Selection.Rows.Count,0).Resize(1,1).Select

e.g. 2 (to select the last column excluding heading)
Range("A1").CurrentRegion.Select
Selection.Offset(1,Selection.Columns.Count-1).Resize(Selection.Rows.Count -
1,1).Select

Hope that helps.

 

[Guest]

I have been able to get my range with the following:
Set SRng = ActiveSheet.UsedRange
Allthough I have to be carefull no errant data exists on the sheet or any
errant data had existed, even if a cell had its data deleted the above code
still finds that cell in the UsedRange?

From ActiveSheet.UsedRange I can then disect the values and set variables
for my range.

sRow = Right(SRng.Rows(x + 1).Address(0, 0), 3)
sRow = Replace(sRow, ":", "")

If Len(sRow) = "2" Then
sRow2 = Left(sRow, 1)
'MsgBox sRow2 'for testing
Else
If Len(sRow) = "3" Then
sRow2 = Left(sRow, 2)
sRow2 = Left(sRow2, 1)
'MsgBox sRow2 'for testing
End If
End If

Not the best, but its working. :fingers crossed:

--
Regards

Rick
XP Pro
Office 2007