Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Hello,
I'm returning some address lines into separate cells, some of which will be blank. Is it possible to delete the blank lines using some form of macro? Cells A1:A6 contain the data. Cell A2 will always be populated, but the remaining 5 can be blank Sample A1 - Address line 1 A2 - A3 - Town A4 - A5 - A6 - County In this sample I would like rows 2, 4 & 5 to be deleted, returning A1 - Address line 1 A3 - Town A6 - County Thanks for any help on this one |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Try this small macro:
Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hello, I'm returning some address lines into separate cells, some of which will be blank. Is it possible to delete the blank lines using some form of macro? Cells A1:A6 contain the data. Cell A2 will always be populated, but the remaining 5 can be blank Sample A1 - Address line 1 A2 - A3 - Town A4 - A5 - A6 - County In this sample I would like rows 2, 4 & 5 to be deleted, returning A1 - Address line 1 A3 - Town A6 - County Thanks for any help on this one |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Hi Gary, that works well, but can I make it for a named range of cells?
The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If End If Next End Sub This assumes that the Named Range has already been established on the worksheet. -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hi Gary, that works well, but can I make it for a named range of cells? The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Hi Gary,
Yes, the range was named, and this has worked a treat! Thank you for your help, Charlotte "Gary''s Student" wrote: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If End If Next End Sub This assumes that the Named Range has already been established on the worksheet. -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hi Gary, that works well, but can I make it for a named range of cells? The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
I think this would give the same results
range("address_block") .specialcells(xlcelltypeblanks).delete "Gary''s Student" wrote: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If End If Next End Sub This assumes that the Named Range has already been established on the worksheet. -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hi Gary, that works well, but can I make it for a named range of cells? The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
Hi,
I think that I may need a bot more on this one. I had forgotten that there will always be a parameter in Col A A B <paramenter Address1 <paramenter Address2 <paramenter <paramenter Address3 <paramenter Address4 any thoughts? "JMB" wrote: I think this would give the same results range("address_block") .specialcells(xlcelltypeblanks).delete "Gary''s Student" wrote: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If End If Next End Sub This assumes that the Named Range has already been established on the worksheet. -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hi Gary, that works well, but can I make it for a named range of cells? The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete blank rows
what do you want to do with the parameter? delete it as well? or is there
some condition that must be met before deleting the blank cells within your named range "address block"? you could delete the entire row with range("address_block") .specialcells(xlcelltypeblanks).entirerow.delete you could test for some condition in the column to the left of "address_block", then delete the entire row '------------------------------------------ Option Explicit Sub test() Dim rngCell As Range For Each rngCell In Range("address_block").SpecialCells(xlCellTypeBlan ks) If rngCell.Offset(0, -1).Value = "some condition" Then rngCell.EntireRow.Delete End If Next rngCell End Sub '------------------------------------------ "Charlotte Howard" wrote: Hi, I think that I may need a bot more on this one. I had forgotten that there will always be a parameter in Col A A B <paramenter Address1 <paramenter Address2 <paramenter <paramenter Address3 <paramenter Address4 any thoughts? "JMB" wrote: I think this would give the same results range("address_block") .specialcells(xlcelltypeblanks).delete "Gary''s Student" wrote: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If End If Next End Sub This assumes that the Named Range has already been established on the worksheet. -- Gary''s Student - gsnu200807 "Charlotte Howard" wrote: Hi Gary, that works well, but can I make it for a named range of cells? The Cells I need to delete will be located around A10:A15 ( I have named them address_block- and there will be blank cells above and below this range which need to remain in place for formatting purposes. C "Gary''s Student" wrote: Try this small macro: Sub rowkiller() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsEmpty(Cells(i, 1)) Then Rows(i).Delete End If Next End Sub -- Gary''s Student - gsnu200807 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Delete all blank rows... | Excel Discussion (Misc queries) | |||
Delete blank rows | Excel Programming | |||
Delete blank row only if 2 consecutive blank rows | Excel Programming |