Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Hi I'm trying to delete all the rows in my worksheet which have any blank cells in them. This doesn't work properly : Columns.Select Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete Can someone suggest an amendment to this? Also if a popup could be worked in for the user to confirm delete I'd be grateful. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Colin Hayes formulated on Friday :
Hi I'm trying to delete all the rows in my worksheet which have any blank cells in them. This doesn't work properly : Columns.Select Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete Can someone suggest an amendment to this? Also if a popup could be worked in for the user to confirm delete I'd be grateful. Thanks. This is trying to delete all cells in every column that's not containing content. Try limiting your selection to UsedRange so columns/rows outside this are left alone. If yo want to delete extra cols/rows outside the UsedRange it might work better to select rows (or cols), delete, then select cols (or rows) and delete again. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
In article , GS writes
Colin Hayes formulated on Friday : Hi I'm trying to delete all the rows in my worksheet which have any blank cells in them. This doesn't work properly : Columns.Select Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete Can someone suggest an amendment to this? Also if a popup could be worked in for the user to confirm delete I'd be grateful. Thanks. This is trying to delete all cells in every column that's not containing content. Try limiting your selection to UsedRange so columns/rows outside this are left alone. If yo want to delete extra cols/rows outside the UsedRange it might work better to select rows (or cols), delete, then select cols (or rows) and delete again. Hi Garry OK Thanks. I see your logic. Interestingly , I can do this manually with success , but a recorded macro gives 'cannot use that command on overlapping selections' and other errors. This is very curious. I think something like Select column A If it has content then Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete Select next column If it has content then Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete do this until column has no used cells. Can you suggest some code that would do this , please? It's the looping element that I'm having trouble with in VBA. Grateful for any help. Best Wishes Colin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
I think the Q is whether you want to delete empty rows, OR any row in
which any cell of any column is blank. Since you're deleting entire rows then maybe.. If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete ...where i is the loop counter ranging from the 1st row to the last row containing data. Alternatively, if you're trying to delete records with incomplete fields of data then... Dim lCols As Long lCols = ActiveSheet.UsedRange.Columns.Count 'Assume 1st row contains header For i = 2 To ActiveSheet.UsedRange.Rows.Count If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete Next 'i ...otherwise, explain exactly what it is you're trying to do! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Since we're deleting rows, we need to start at the bottom and work
up... For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1 If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete Next 'i ...otherwise the shift after delete will skip a row if going downward! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
In article , GS writes
Since we're deleting rows, we need to start at the bottom and work up... For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1 If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete Next 'i ..otherwise the shift after delete will skip a row if going downward! Hi Garry OK many thank for that. It's working fine. Yes , I do want to delete all rows which have any blank cells at all in them. I added a couple of lines to top and bottom to switch screen updating off and on. The final macro I'm running looks like this ; Sub A_Delete_All_Rows_Containing_Blank_Cells() Application.ScreenUpdating = False Dim lCols As Long lCols = ActiveSheet.UsedRange.Columns.Count 'Assume 1st row contains header For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1 If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete Next 'i Application.ScreenUpdating = True End Sub I assume this is what you intended , if I've understood you right. I'm grateful again for your time and expertise. Best Wishes Colin |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Glad you got it working! I'm happy to be of help...
-- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
In article , GS writes
Glad you got it working! I'm happy to be of help... OK thanks Garry. BTW Curiously when I effect the same change manually (F5 - Special - Blanks - Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546 rows. Using the macro it leaves 4244 lines from the same worksheet. This is a little mysterious , I find. The worksheet has no formulas or merged cells or false spaces in cells. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Colin Hayes wrote on 5/20/2012 :
In article , GS writes Glad you got it working! I'm happy to be of help... OK thanks Garry. BTW Curiously when I effect the same change manually (F5 - Special - Blanks - Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546 rows. Using the macro it leaves 4244 lines from the same worksheet. This is a little mysterious , I find. The worksheet has no formulas or merged cells or false spaces in cells. Are there any empty cells in columns of the 4359 rows left using F5...? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Colin Hayes wrote on 5/20/2012 :
In article , GS writes Glad you got it working! I'm happy to be of help... OK thanks Garry. BTW Curiously when I effect the same change manually (F5 - Special - Blanks - Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546 rows. Using the macro it leaves 4244 lines from the same worksheet. This is a little mysterious , I find. The worksheet has no formulas or merged cells or false spaces in cells. Another thought... The code acts on the UsedRange. Not sure what F5 acts on and so the code might remove more if UsedRange extends beyond the actual data. You can find the last row/col limits via 'Ctrl+End'! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
BTW Curiously when I effect the same change manually (F5 - Special - Blanks -
Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546 rows. Using the macro it leaves 4244 lines from the same worksheet. This is a little mysterious , I find. The worksheet has no formulas or merged cells or false spaces in cells. Another thought... The code acts on the UsedRange. Not sure what F5 acts on and so the code might remove more if UsedRange extends beyond the actual data. You can find the last row/col limits via 'Ctrl+End'! Hi Garry Some very interesting results. I used the manual F5 method and it selected all the blank cells in the worksheet. I then deleted the whole row for each and it left 4539 rows as before. I then used the F5 method again on the amended worksheet and it found more blank cells! This is very curious , as I thought all the blanks would have been selected the first time around. On deleting the whole row for each , it left 4244 rows. This is the same as the macro leaves. I'm sure there must be a logic for this phenomenon. Clearly the macro achieves the same result as the manual method , but does it in one pass rather than two. Ctrl-End does find the bottom right extremity of used range , as it should. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting rows which contain blank cells
Colin Hayes brought next idea :
BTW Curiously when I effect the same change manually (F5 - Special - Blanks - Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546 rows. Using the macro it leaves 4244 lines from the same worksheet. This is a little mysterious , I find. The worksheet has no formulas or merged cells or false spaces in cells. Another thought... The code acts on the UsedRange. Not sure what F5 acts on and so the code might remove more if UsedRange extends beyond the actual data. You can find the last row/col limits via 'Ctrl+End'! Hi Garry Some very interesting results. I used the manual F5 method and it selected all the blank cells in the worksheet. I then deleted the whole row for each and it left 4539 rows as before. I then used the F5 method again on the amended worksheet and it found more blank cells! This is very curious , as I thought all the blanks would have been selected the first time around. On deleting the whole row for each , it left 4244 rows. This is the same as the macro leaves. I'm sure there must be a logic for this phenomenon. Clearly the macro achieves the same result as the manual method , but does it in one pass rather than two. Ctrl-End does find the bottom right extremity of used range , as it should. That's interesting to know! Thanks for reporting back... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting Blank Cells then deleting those rows | Excel Programming | |||
Deleting rows when certain cells are blank | Excel Programming | |||
Deleting blank (Cells/Rows) in Excel-VBA | Excel Programming | |||
Deleting rows with blank cells | Excel Programming | |||
Deleting rows with blank cells | Excel Worksheet Functions |