Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
Hi,
I have a sheet that I have imported data to, it contains approx 35000 rows and 7 columns. 10000 of these rows either have a empty cell in column A or has text in it and I want to delete the entire row Is there a quicker way then checking each cell in column A and then deleteing that row. Sub CheckForNumber() Dim Lastrow As Long Dim i As Long Lastrow = Cells(65536, 1).End(xlUp).Row + 1 For i = Lastrow To 1 Step -1 Range("a" & i).Activate If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete End If Next End Sub I have tried using the following to delete the empty cells 'On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete 'On Error GoTo 0 but it deletes everything. I have used this in another workbook and it works OK. I have also tried filtering the data and only showing the blanks in column A and then deleting visible rows, but I get an error message that it is too complex to do. Thanks in advance Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
Hi Neil,
The reason your macro runs slow is because you did not turn off screen updating and turn off calculation. See Slow Response ... http://www.mvps.org/dmcritchie/excel/slowresp.htm But in answer to your question there is a faster way without a loop and any need to turn off screen updating or calculation based on the following:. This macro will delete the empty cell cells in a selection without loops. Sub DelEmpty() 'Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) End Sub For your request: You want to delete the entire row based on column A having empty cells Sub DelRows_on_EmptyA() 'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete (xlShiftUp) End Sub Delete Cells/Rows in Range, based on empty cells, or cells with specific values http://www.mvps.org/dmcritchie/excel/delempty.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Neil" wrote in message ... Hi, I have a sheet that I have imported data to, it contains approx 35000 rows and 7 columns. 10000 of these rows either have a empty cell in column A or has text in it and I want to delete the entire row Is there a quicker way then checking each cell in column A and then deleteing that row. Sub CheckForNumber() Dim Lastrow As Long Dim i As Long Lastrow = Cells(65536, 1).End(xlUp).Row + 1 For i = Lastrow To 1 Step -1 Range("a" & i).Activate If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete End If Next End Sub I have tried using the following to delete the empty cells 'On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete 'On Error GoTo 0 but it deletes everything. I have used this in another workbook and it works OK. I have also tried filtering the data and only showing the blanks in column A and then deleting visible rows, but I get an error message that it is too complex to do. Thanks in advance Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
David,
Thanks for the help with the loop, but as for the special cells, it still deletes all rows not just the ones with a blank cell in column A, any ideas. Neil "David McRitchie" wrote in message ... Hi Neil, The reason your macro runs slow is because you did not turn off screen updating and turn off calculation. See Slow Response ... http://www.mvps.org/dmcritchie/excel/slowresp.htm But in answer to your question there is a faster way without a loop and any need to turn off screen updating or calculation based on the following:. This macro will delete the empty cell cells in a selection without loops. Sub DelEmpty() 'Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp) End Sub For your request: You want to delete the entire row based on column A having empty cells Sub DelRows_on_EmptyA() 'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete (xlShiftUp) End Sub Delete Cells/Rows in Range, based on empty cells, or cells with specific values http://www.mvps.org/dmcritchie/excel/delempty.htm -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Neil" wrote in message ... Hi, I have a sheet that I have imported data to, it contains approx 35000 rows and 7 columns. 10000 of these rows either have a empty cell in column A or has text in it and I want to delete the entire row Is there a quicker way then checking each cell in column A and then deleteing that row. Sub CheckForNumber() Dim Lastrow As Long Dim i As Long Lastrow = Cells(65536, 1).End(xlUp).Row + 1 For i = Lastrow To 1 Step -1 Range("a" & i).Activate If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete End If Next End Sub I have tried using the following to delete the empty cells 'On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete 'On Error GoTo 0 but it deletes everything. I have used this in another workbook and it works OK. I have also tried filtering the data and only showing the blanks in column A and then deleting visible rows, but I get an error message that it is too complex to do. Thanks in advance Neil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
Hi Neil,
The macro to remove all rows that are blank in Column A works fine for me on Excel 2000. If it does not work for you, I would expect you are running Excel 95 and it is mentioned that these macros will not work on Excel 95. With Excel 95 you would need a macro like your original macro with the extra coding to make run faster. http://www.mvps.org/dmcritchie/excel....htm#emptyrows If it is not Excel 95 or earlier that is the problem then expect you might not be scrolled all the way to the left where Column A is visible. Sub DelRows_on_EmptyA() 'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete (xlShiftUp) End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Neil Eves" wrote in message ... David, Thanks for the help with the loop, but as for the special cells, it still deletes all rows not just the ones with a blank cell in column A, any ideas. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
David,
I am using Excel 2000 and I have two sheets that the data is imported to from two different dat files(Mainframe generated), it works Ok on the first sheet but not on the second. I have another workbook importing similar data into two sheets and it works OK there. I am at a loss as to why it doesn't work on this one sheet. Neil "David McRitchie" wrote in message ... Hi Neil, The macro to remove all rows that are blank in Column A works fine for me on Excel 2000. If it does not work for you, I would expect you are running Excel 95 and it is mentioned that these macros will not work on Excel 95. With Excel 95 you would need a macro like your original macro with the extra coding to make run faster. http://www.mvps.org/dmcritchie/excel....htm#emptyrows If it is not Excel 95 or earlier that is the problem then expect you might not be scrolled all the way to the left where Column A is visible. Sub DelRows_on_EmptyA() 'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete (xlShiftUp) End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Neil Eves" wrote in message ... David, Thanks for the help with the loop, but as for the special cells, it still deletes all rows not just the ones with a blank cell in column A, any ideas. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
After a lot of trials by copying data to a new sheet and then running the
macro Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete It appears that it will only work with a maximum of 10148 blank cells in column A of my worksheet after that it deletes all data in the worksheet. I ran it with 30260 rows with 10148 blank cells and it worked I ran it with 30261 rows with 10149 blank cells and it did not work I ran it with 30261 rows with 10148 blank cells and it worked If I create a new sheet and go down to A10149 and enter some data in the following cells in column a it appears to work. If I put data in every second cell in column A down to row 25000 it deletes everything. Is this a limit or is it just that it is too complex to work? Neil "John Carter" wrote in message ... Neil, Try manually selecting column A then enter edit--goto--special cells--blanks. Then scroll down and see which cells are actually selected. Sometimes imported text files appear to have data but are actually blank. Hope this helps John "Neil Eves" wrote in message ... David, I am using Excel 2000 and I have two sheets that the data is imported to from two different dat files(Mainframe generated), it works Ok on the first sheet but not on the second. I have another workbook importing similar data into two sheets and it works OK there. I am at a loss as to why it doesn't work on this one sheet. Neil "David McRitchie" wrote in message ... Hi Neil, The macro to remove all rows that are blank in Column A works fine for me on Excel 2000. If it does not work for you, I would expect you are running Excel 95 and it is mentioned that these macros will not work on Excel 95. With Excel 95 you would need a macro like your original macro with the extra coding to make run faster. http://www.mvps.org/dmcritchie/excel....htm#emptyrows If it is not Excel 95 or earlier that is the problem then expect you might not be scrolled all the way to the left where Column A is visible. Sub DelRows_on_EmptyA() 'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998 Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete (xlShiftUp) End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Neil Eves" wrote in message ... David, Thanks for the help with the loop, but as for the special cells, it still deletes all rows not just the ones with a blank cell in column A, any ideas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work around to SpecialCells(xlCellTypeBlanks)... | Excel Discussion (Misc queries) | |||
Specialcells | Charts and Charting in Excel | |||
VBA ON ERROR does not work with SPECIALCELLS | Excel Discussion (Misc queries) | |||
.SpecialCells(xlLastCell).Select | Excel Programming | |||
specialcells and toolbox button click event | Excel Programming |