Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
Hi,
I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
try:
Sub macro_der() Dim i As Long, nLastRow As Long Set r = ActiveSheet.UsedRange nLastRow = r.Rows.Count + r.Row - 1 Set r = Rows(nLastRow + 1) For i = 1 To nLastRow If Application.CountA(Rows(i)) = 0 Then Set r = Union(r, Rows(i)) End If Next r.Delete End Sub -- Gary''s Student - gsnu200776 "Youlan" wrote: Hi, I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
Hi
This routine will look at cells in column A and delete entire row if the cell is empty. Sub RemoveBlankRows() Dim TargetRange As Range Const TargetColumn As Integer = 1 'Check fo empty cells in coloumn A Set TargetRange = Range(Cells(1, TargetColumn), Cells(65536, TargetColumn).End(xlUp)) rowcount = TargetRange.Rows.Count For r = rowcount To 1 Step -1 If Cells(r, 1).Value = "" Then Rows(r).Delete Next End Sub Regards, Per "Youlan" skrev i meddelelsen ... Hi, I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
Sub DeleteBlankRows()
Cells.EntireRow.Hidden=False Cells.ColumnDifferences(Cells(Cells.Count)).Entire Row.Hidden=True Cells.SpecialCells(xlCellTypeVisible).Delete Cells.EntireRow.Hidden=False End Sub "Youlan" wrote: Hi, I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
Without a macro...........Select a column and
F5SpecialBlanksOKEditDeleteEntire Rows Gord Dibben MS Excel MVP On Fri, 28 Mar 2008 08:17:01 -0700, Youlan wrote: Hi, I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to delete blank rows in a data range
Thank you very much, it"s nice and simple and was very helpful to me.
Diana Vazian "Gord Dibben" wrote: Without a macro...........Select a column and F5SpecialBlanksOKEditDeleteEntire Rows Gord Dibben MS Excel MVP On Fri, 28 Mar 2008 08:17:01 -0700, Youlan wrote: Hi, I'm using Excel 2002 and I'm working in a spreasheet with data copied from an external source. I need to automatically delete the blank rows without having to go through the entire document. Also their is no set format to when and how many blank rows are in the document. Can anyone help? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reformating data- how to delete alternate blank rows quickly | Excel Discussion (Misc queries) | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
delete rows in range - macro | Excel Worksheet Functions | |||
Delete blank rows Macro | Excel Discussion (Misc queries) |