| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop help
In the code below I am able to see the cell.address of cells with values
(true) and cells without values (false). Of this I can't seem to capture when the last empty cell occured so I can set a range of first cell with a value and last cell without a value. As an example (Column A only); row1 would be true, then row 5 would be true all rows in between would be false. How can I get the range of row 1 thru row 4? I initially thought I would get the address of the last cell before finding a cell that is true again. So much for that theory. '====== Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c < "" Then 'if "c" is something get cell address vNameCellAddress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vNameCellAddress 'for testing End If If c = "" Then vEmptyCellAddress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vEmptyCellAddress 'for testing End If MsgBox vNameCellAddress & " " & vEmptyCellAddress Next End Sub '====== Thanks in advance -- Regards VBA.Noob.Confused XP Pro Office 2007 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
Is this what you're wanting?
Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in 'Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c.Value < "" And c.Offset(1, 0).Value = "" Then 'if "c" is something get cell address vnamecelladdress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vnamecelladdress 'for testing End If If c.Value = "" And c.Offset(1, 0).Value < "" Then vemptycelladdress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vemptycelladdress 'for testing End If If vnamecelladdress < "" And vemptycelladdress < "" Then MsgBox vnamecelladdress & " " & vemptycelladdress vnamecelladdress = "" vemptycelladdress = "" End If Next End Sub Ken Johnson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
This is a little antiquated but it will find the first empty cell and the
last empty cell in a consecutive series of empty cells, which appears to be what you are after. If you want the last empty cell in a non-consecutive range of empty cells, that would take a little more doing. Sub findEmptyRng() lstRw = Cells(Rows.Count, 1).End(xlUp).Row Range("A2").Activate With ActiveSheet.Range("A2:A" & lstRw) Do Until ActiveCell = "" ActiveCell.Offset(1, 0).Activate Loop FirstEmpty = ActiveCell.Address LastEmpty = ActiveCell.End(xlDown).Offset(-1, 0).Address MsgBox "Empty Range is " & FirstEmpty & ":" & LastEmpty End With End Sub "Rick S." wrote: In the code below I am able to see the cell.address of cells with values (true) and cells without values (false). Of this I can't seem to capture when the last empty cell occured so I can set a range of first cell with a value and last cell without a value. As an example (Column A only); row1 would be true, then row 5 would be true all rows in between would be false. How can I get the range of row 1 thru row 4? I initially thought I would get the address of the last cell before finding a cell that is true again. So much for that theory. '====== Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c < "" Then 'if "c" is something get cell address vNameCellAddress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vNameCellAddress 'for testing End If If c = "" Then vEmptyCellAddress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vEmptyCellAddress 'for testing End If MsgBox vNameCellAddress & " " & vEmptyCellAddress Next End Sub '====== Thanks in advance -- Regards VBA.Noob.Confused XP Pro Office 2007 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
I'm having trouble figuring out exactly what you want. Lets pretend this is
the contents of Row A.. Row 1: aaa Row 2: bbb Row 3: ccc Row 4: Row 5: ddd Row 6: eeee Row 7: Row 8: Row 9: fff Row 10: ggg Row 11: Row 12: ..... ..... Everything after row 11 is empty. Which row did you want to find? Rick "Rick S." wrote in message ... In the code below I am able to see the cell.address of cells with values (true) and cells without values (false). Of this I can't seem to capture when the last empty cell occured so I can set a range of first cell with a value and last cell without a value. As an example (Column A only); row1 would be true, then row 5 would be true all rows in between would be false. How can I get the range of row 1 thru row 4? I initially thought I would get the address of the last cell before finding a cell that is true again. So much for that theory. '====== Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c < "" Then 'if "c" is something get cell address vNameCellAddress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vNameCellAddress 'for testing End If If c = "" Then vEmptyCellAddress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vEmptyCellAddress 'for testing End If MsgBox vNameCellAddress & " " & vEmptyCellAddress Next End Sub '====== Thanks in advance -- Regards VBA.Noob.Confused XP Pro Office 2007 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
Rows 3 and 4 would be a range, rows 6 thru 8 would be a range and rows 10
thru 12 would be a range. I did forget to mention the last row I want to count would have "END" in it. So in your example Row 13 would be "END" (no quoates). At that point I would offset up one cell. Instead of having the same text for each row: Row 1: aaa Row 2: bbb Row 3: ccc Row 4: ccc Row 5: ddd Row 6: eeee Row 7: eeee Row 8: eeee Row 9: fff Row 10: ggg Row 11: ggg Row 12: ggg Row 13: END The rows have been created as in your example. Please excuse the confusion, it was a frustrating day. -- Regards VBA.Noob.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I'm having trouble figuring out exactly what you want. Lets pretend this is the contents of Row A.. Row 1: aaa Row 2: bbb Row 3: ccc Row 4: Row 5: ddd Row 6: eeee Row 7: Row 8: Row 9: fff Row 10: ggg Row 11: Row 12: ..... ..... Everything after row 11 is empty. Which row did you want to find? Rick "Rick S." wrote in message ... In the code below I am able to see the cell.address of cells with values (true) and cells without values (false). Of this I can't seem to capture when the last empty cell occured so I can set a range of first cell with a value and last cell without a value. As an example (Column A only); row1 would be true, then row 5 would be true all rows in between would be false. How can I get the range of row 1 thru row 4? I initially thought I would get the address of the last cell before finding a cell that is true again. So much for that theory. '====== Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c < "" Then 'if "c" is something get cell address vNameCellAddress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vNameCellAddress 'for testing End If If c = "" Then vEmptyCellAddress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vEmptyCellAddress 'for testing End If MsgBox vNameCellAddress & " " & vEmptyCellAddress Next End Sub '====== Thanks in advance -- Regards VBA.Noob.Confused XP Pro Office 2007 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
I modified your code to show the range I would like to find if the rows had
data as follows: Row1: aaa Row2: Row3: Row4: Row5: bbb The following code would find a range of Row1 thru Row4. '====== Sub findEmptyRng() Dim lstRw As Variant Dim FirstEmpty As Variant Dim LastEmpty As Variant lstRw = Cells(Rows.Count, 1).End(xlUp).Row Range("A2").Activate With ActiveSheet.Range("A3:A" & lstRw) Do Until ActiveCell = "" ActiveCell.Offset(1, 0).Activate Loop FirstEmpty = ActiveCell.Offset(-1, 0).Address LastEmpty = ActiveCell.End(xlDown).Offset(-1, 0).Address MsgBox "Empty Range is " & FirstEmpty & ":" & LastEmpty End With End Sub '====== This code ends on the first range it finds? there can be many ranges in my workbook. -- Regards VBA.Noob.Confused XP Pro Office 2007 "JLGWhiz" wrote: This is a little antiquated but it will find the first empty cell and the last empty cell in a consecutive series of empty cells, which appears to be what you are after. If you want the last empty cell in a non-consecutive range of empty cells, that would take a little more doing. Sub findEmptyRng() lstRw = Cells(Rows.Count, 1).End(xlUp).Row Range("A2").Activate With ActiveSheet.Range("A2:A" & lstRw) Do Until ActiveCell = "" ActiveCell.Offset(1, 0).Activate Loop FirstEmpty = ActiveCell.Address LastEmpty = ActiveCell.End(xlDown).Offset(-1, 0).Address MsgBox "Empty Range is " & FirstEmpty & ":" & LastEmpty End With End Sub "Rick S." wrote: In the code below I am able to see the cell.address of cells with values (true) and cells without values (false). Of this I can't seem to capture when the last empty cell occured so I can set a range of first cell with a value and last cell without a value. As an example (Column A only); row1 would be true, then row 5 would be true all rows in between would be false. How can I get the range of row 1 thru row 4? I initially thought I would get the address of the last cell before finding a cell that is true again. So much for that theory. '====== Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c < "" Then 'if "c" is something get cell address vNameCellAddress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vNameCellAddress 'for testing End If If c = "" Then vEmptyCellAddress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vEmptyCellAddress 'for testing End If MsgBox vNameCellAddress & " " & vEmptyCellAddress Next End Sub '====== Thanks in advance -- Regards VBA.Noob.Confused XP Pro Office 2007 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
This appears to do what I was attempting (Live test on backup worksheet)!
-- Regards VBA.Noob.Confused XP Pro Office 2007 "Ken Johnson" wrote: Is this what you're wanting? Sub RowCount() lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in 'Column A For Each c In ActiveSheet.Range("A3:A" & lrow) If c.Value < "" And c.Offset(1, 0).Value = "" Then 'if "c" is something get cell address vnamecelladdress = c.Address 'get cell address of cell with a value MsgBox "True" & " " & vnamecelladdress 'for testing End If If c.Value = "" And c.Offset(1, 0).Value < "" Then vemptycelladdress = c.Address 'get cell address of cell with a value MsgBox "False" & " " & vemptycelladdress 'for testing End If If vnamecelladdress < "" And vemptycelladdress < "" Then MsgBox vnamecelladdress & " " & vemptycelladdress vnamecelladdress = "" vemptycelladdress = "" End If Next End Sub Ken Johnson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop help
Thanks to all who contributed!
Ken Johnson, Thank you! -- Regards VBA.Noob.Confused XP Pro Office 2007 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| (Complex) Loop within loop to create worksheets | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Linear Mode
