Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
I need code that if column I ,starting with row 2 (with unknown number of
rows) is blank, then puts an a in column A. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
Sub PutA()
Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message link.net... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
This puts a's all the way to row 500. My test data goes to row 171.
"Tim Zych" wrote in message ... Sub PutA() Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message link.net... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
Hi
Try Sub aaa() Range("i65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell) Then Cells(ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub This will find the last entry in column I and work up from there. Tony -----Original Message----- This puts a's all the way to row 500. My test data goes to row 171. "Tim Zych" wrote in message ... Sub PutA() Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message news:1uw1c.28856 ... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
This doesn't work if last rows are blank. Column B always has something in
it if this helps. Perhaps code to find used range can look at B, but look for Blanks in I??? "acw" wrote in message ... Hi Try Sub aaa() Range("i65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell) Then Cells(ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub This will find the last entry in column I and work up from there. Tony -----Original Message----- This puts a's all the way to row 500. My test data goes to row 171. "Tim Zych" wrote in message ... Sub PutA() Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message news:1uw1c.28856 ... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
Hi
Ok revised using column B Sub aaa() Range("B65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell.offset(0,7)) Then Cells (ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub Tony -----Original Message----- This doesn't work if last rows are blank. Column B always has something in it if this helps. Perhaps code to find used range can look at B, but look for Blanks in I??? "acw" wrote in message ... Hi Try Sub aaa() Range("i65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell) Then Cells(ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub This will find the last entry in column I and work up from there. Tony -----Original Message----- This puts a's all the way to row 500. My test data goes to row 171. "Tim Zych" wrote in message ... Sub PutA() Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message news:1uw1c.28856 ... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
if column I is blank, then puts an "a" in column A.
Thanks Tony, works great.
"acw" wrote in message ... Hi Ok revised using column B Sub aaa() Range("B65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell.offset(0,7)) Then Cells (ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub Tony -----Original Message----- This doesn't work if last rows are blank. Column B always has something in it if this helps. Perhaps code to find used range can look at B, but look for Blanks in I??? "acw" wrote in message ... Hi Try Sub aaa() Range("i65536").End(xlUp).Select While ActiveCell.Row 1 If IsEmpty(ActiveCell) Then Cells(ActiveCell.Row, 1) = "a" ActiveCell.Offset(-1, 0).Select Wend End Sub This will find the last entry in column I and work up from there. Tony -----Original Message----- This puts a's all the way to row 500. My test data goes to row 171. "Tim Zych" wrote in message ... Sub PutA() Dim rng As Range, cell As Range Set rng = Application.Intersect( _ Range("I2:I65536"), ActiveSheet.UsedRange) If Not rng Is Nothing Then For Each cell In rng.Cells If Len(cell.Value) = 0 Then Cells(cell.Row, "A").Value = "a" End If Next cell End If End Sub "Randy Reese" wrote in message news:1uw1c.28856 ... I need code that if column I ,starting with row 2 (with unknown number of rows) is blank, then puts an a in column A. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Auto Filter - how to collapse "non-blank" cells in a column? | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
Removing "Blank Cells" from a column | Excel Worksheet Functions |