Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting the blank rows from the sheet
Hi
Thanx for replying me in time for my earlier question. I have a data of 15000 rows. My problem is there are spaces of a row or two between each records. I could have sort it to remove the blank rows in between. But do not want to change the order of the records. pls suggest the right way to remove blank rows from the records in short period of time. thanx in advance. Manu |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting the blank rows from the sheet
Hi,
I have assumed that if the cell in column A is empty then this identifies a blank row. If that is correct then right click the sheet tab, view code and paste this in:- Sub surface() With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For x = LastRow To 1 Step -1 Cells(x, 1).Select If Cells(x, 1).Value = "" Then Selection.EntireRow.Delete End If Next End Sub Mike "manu" wrote: Hi Thanx for replying me in time for my earlier question. I have a data of 15000 rows. My problem is there are spaces of a row or two between each records. I could have sort it to remove the blank rows in between. But do not want to change the order of the records. pls suggest the right way to remove blank rows from the records in short period of time. thanx in advance. Manu |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting the blank rows from the sheet
Its working.
Thanx a lot. rgds, Manu "Mike H" wrote: Hi, I have assumed that if the cell in column A is empty then this identifies a blank row. If that is correct then right click the sheet tab, view code and paste this in:- Sub surface() With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For x = LastRow To 1 Step -1 Cells(x, 1).Select If Cells(x, 1).Value = "" Then Selection.EntireRow.Delete End If Next End Sub Mike "manu" wrote: Hi Thanx for replying me in time for my earlier question. I have a data of 15000 rows. My problem is there are spaces of a row or two between each records. I could have sort it to remove the blank rows in between. But do not want to change the order of the records. pls suggest the right way to remove blank rows from the records in short period of time. thanx in advance. Manu |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting the blank rows from the sheet
Personally, I would use:
Sub surface() Dim rng As Range Set rng = Application.Intersect(Cells.Columns(1), ActiveSheet.UsedRange) On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e On Error GoTo 0 End Sub -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Mike H" wrote: Hi, I have assumed that if the cell in column A is empty then this identifies a blank row. If that is correct then right click the sheet tab, view code and paste this in:- Sub surface() With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For x = LastRow To 1 Step -1 Cells(x, 1).Select If Cells(x, 1).Value = "" Then Selection.EntireRow.Delete End If Next End Sub Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
deleting the blank rows from the sheet
Select a column and F5SpecialBlanksOK
EditDeleteEntire Row. Gord Dibben MS Excel MVP On Mon, 9 Jul 2007 01:12:07 -0700, manu wrote: Hi Thanx for replying me in time for my earlier question. I have a data of 15000 rows. My problem is there are spaces of a row or two between each records. I could have sort it to remove the blank rows in between. But do not want to change the order of the records. pls suggest the right way to remove blank rows from the records in short period of time. thanx in advance. Manu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
How paste Rows with Blank cells into New sheet in the same Workboo | Excel Discussion (Misc queries) | |||
Deleting blank rows | Excel Worksheet Functions | |||
Deleting Blank Rows | New Users to Excel | |||
Deleting rows with blank cells | Excel Worksheet Functions |