| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
delete rows with empty cells
I need to delete rows when certain columns have empty cells.
ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Slohcin
I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Nick,
Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Slohcin
That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will do that for you. Vaya con Dios, Chuck, CABGx3 "Slohcin" wrote: I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Sub deleteemptyrows()
For i = 17 to 2 step -1 'from bottom up If Application.CountA(Range(Cells(i, 1), Cells(i, 3))) < 1 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Nick,
Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Slohcin
Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Nick,
Thanks for your help. It appears that columns B,C & D are not blank but contain a space. I have amended your original formula to cater for this and it now works. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Slohcin
Glad it works. Did you handle the space " " or did you put a TRIM around each reference =IF(AND(TRIM(B1)=""............ -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your help. It appears that columns B,C & D are not blank but contain a space. I have amended your original formula to cater for this and it now works. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Nick,
Did it with space " ", is this the best way? I would like to run this as a macro, is this possible? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Glad it works. Did you handle the space " " or did you put a TRIM around each reference =IF(AND(TRIM(B1)=""............ -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your help. It appears that columns B,C & D are not blank but contain a space. I have amended your original formula to cater for this and it now works. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
Slohcin
I would rather acknowledge they were there and deal with them with say TRIM. Here is some code that should work for data in A:D Sub DeleteRowsWithBlankBD() Dim lLastRow As Long Dim x As Integer lLastRow = Range("A65536").End(xlUp).Row For x = lLastRow To 1 Step -1 If Trim(Range("B" & x)) & Trim(Range("C" & x)) & Trim(Range("D" & x)) = "" Then Range("A" & x).EntireRow.Delete End If Next x End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Did it with space " ", is this the best way? I would like to run this as a macro, is this possible? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Glad it works. Did you handle the space " " or did you put a TRIM around each reference =IF(AND(TRIM(B1)=""............ -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your help. It appears that columns B,C & D are not blank but contain a space. I have amended your original formula to cater for this and it now works. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
I guess you didn't see my post?
-- Don Guillett SalesAid Software "Slohcin" wrote in message ... Nick, Did it with space " ", is this the best way? I would like to run this as a macro, is this possible? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Glad it works. Did you handle the space " " or did you put a TRIM around each reference =IF(AND(TRIM(B1)=""............ -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your help. It appears that columns B,C & D are not blank but contain a space. I have amended your original formula to cater for this and it now works. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin Then the formula should work. Can you set up helper columns to return the contents of cells in columns B, C &D thus =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(D1) and copy down Where a cell is 'thought' by eye to be empty you should get TRUE. If you get TRUE in any row in B, C & D the formula using that same row should put an x in the cell to sort on -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Column A will always have an entry. As I tried to illustrate in my example, Columns B,C+D may or may not have an entry. I am looking to delete any row where B,C +D are empty/blank. There are no formulas used. -- Thanks Slohcin "Nick Hodge" wrote: Slohcin That's what happen here. Are you sure A,B and C are empty? No formula, or similar? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... Nick, Thanks for your reply. I have tried what you suggested, but nothing happens. I was expecting an "x" in column E, but get nothing. What am I doing wrong? -- Thanks Slohcin "Nick Hodge" wrote: Slohcin I would set up a helper column in E and enter =IF(AND(B1="",C1="",D1=""),"x","") Then copy down and sort on that column and delete all the rows with an x in -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
delete rows with empty cells
This will take care of trimming.
Sub emptycells() For i = 14 To 17 For j = 2 To 4 Cells(i, j).Value = Trim(Cells(i, j)) Next j If Application.CountA(Range(Cells(i, 2), Cells(i, 4))) < 1 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Let's modify a bit Sub deleteemptyrows() For i = cells(rows.count,"a").end(xlup).row to 2 step -1 If Application.CountA(Range(Cells(i, 2), Cells(i, 4))) < 1 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Sub deleteemptyrows() For i = 17 to 2 step -1 'from bottom up If Application.CountA(Range(Cells(i, 1), Cells(i, 3))) < 1 _ Then Rows(i).Delete Next i End Sub -- Don Guillett SalesAid Software "Slohcin" wrote in message ... I need to delete rows when certain columns have empty cells. ColA ColB ColC ColD data y y data data y y data y y y What I need to do is delete row2 because columns B,C & D are empty. How can I do this? -- Thanks Slohcin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete empty rows in workbook | Excel Worksheet Functions | |||
| How to count blank cells (Rows) between Non-Blank cells in Col "A" | Excel Discussion (Misc queries) | |||
| resetting last cell | Excel Discussion (Misc queries) | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
| Macro to hide rows with empty cells | Excel Worksheet Functions | |||
Linear Mode
