Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find used range and ignore blank cells when running macro?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find used range and ignore blank cells when running macro?
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. --- HTH Roger Shaftesbury (UK) "Rick S." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find used range and ignore blank cells when running macro?
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. "Rick S." wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find used range and ignore blank cells when running macro?
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 "Rick S." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore blank cells in a named range when using dependent data validation | Excel Discussion (Misc queries) | |||
Ignore Blank Cells | Excel Programming | |||
find range of non-blank cells in colum | Excel Programming | |||
Running a macro if any data is entered in a range of cells | Excel Worksheet Functions | |||
Blank cells in named range- how to ignore them when making my graph? Help plz! | Excel Discussion (Misc queries) |