I exported data from a Crystal Report into Excel. During the export process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null values
or space?

Thanks

The easiest way is to select all the cells and then sort, which puts blanks (depends on what you
mean by NULL values) to the bottom. Of course, if you sort, that would change the order, so
sometimes a macro like this might be better, which will delete any row where column A is truly
blank:

Sub DeleteRowsBasedOnBlankCellsInColumnA()
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null values
or space?

Thanks

I have a similar problem with an import into Excel2007. 45,000+ rows
populating just col A & B and I wish to delete only rows where both col A and
col B are blank. I tried modifying the sub below to the range "A:B" but
deletes everything. Can someone help?

"Bernie Deitrick" wrote:

The easiest way is to select all the cells and then sort, which puts blanks (depends on what you
mean by NULL values) to the bottom. Of course, if you sort, that would change the order, so
sometimes a macro like this might be better, which will delete any row where column A is truly
blank:

Sub DeleteRowsBasedOnBlankCellsInColumnA()
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null values
or space?

Thanks

First, you're far better off to start a new thread for problems like this.
Attaching your message to a thread that's over two months old significantly
limits the help you might get. Most people don't look at threads this old.

Second, if this is a one-time requirement, I would use the Data Filter.
Select all the blank rows in Column A, then the blank rows in Column B. Now
you have only the rows where both A and B are blank. Now delete them.

Regards,
Fred.

"OzWaz" wrote in message
...
I have a similar problem with an import into Excel2007. 45,000+ rows
populating just col A & B and I wish to delete only rows where both col A
and
col B are blank. I tried modifying the sub below to the range "A:B" but
deletes everything. Can someone help?

"Bernie Deitrick" wrote:

The easiest way is to select all the cells and then sort, which puts
blanks (depends on what you
mean by NULL values) to the bottom. Of course, if you sort, that would
change the order, so
sometimes a macro like this might be better, which will delete any row
where column A is truly
blank:

Sub DeleteRowsBasedOnBlankCellsInColumnA()
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like
to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without
selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

One way would be to use datafilterautofilterfilter both on blanks. I just
recorded this. Clean it up to remove selections

Sub Macro1()
'
' Macro1 Macro
'

'

Range("A1:B8").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=1, Criteria1:="="
ActiveSheet.Range("$A$1:$B$8").AutoFilter Field:=2, Criteria1:="="
Range("A2:B6").Select
Selection.EntireRow.Delete
Selection.AutoFilter
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"OzWaz" wrote in message
...
I have a similar problem with an import into Excel2007. 45,000+ rows
populating just col A & B and I wish to delete only rows where both col A
and
col B are blank. I tried modifying the sub below to the range "A:B" but
deletes everything. Can someone help?

"Bernie Deitrick" wrote:

The easiest way is to select all the cells and then sort, which puts
blanks (depends on what you
mean by NULL values) to the bottom. Of course, if you sort, that would
change the order, so
sometimes a macro like this might be better, which will delete any row
where column A is truly
blank:

Sub DeleteRowsBasedOnBlankCellsInColumnA()
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like
to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without
selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

Select a column where you can be SURE that a blank cell means you want to
delete that row. Select the whole column, do edit / Go to / Special /
blanks, then hit edit / delete / entire row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

Where are these "do edit /Go to / special" commands? Are they entered in
macro or is this from a pull down menu. Can you be more specific?

"Ken Wright" wrote:

Select a column where you can be SURE that a blank cell means you want to
delete that row. Select the whole column, do edit / Go to / Special /
blanks, then hit edit / delete / entire row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

BTW, I checked the date of this post... I am using office 2007.

"Ken Wright" wrote:

Select a column where you can be SURE that a blank cell means you want to
delete that row. Select the whole column, do edit / Go to / Special /
blanks, then hit edit / delete / entire row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

Hi,

Ctrl+G Special.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dael" wrote in message
...
BTW, I checked the date of this post... I am using office 2007.

"Ken Wright" wrote:

Select a column where you can be SURE that a blank cell means you want to
delete that row. Select the whole column, do edit / Go to / Special /
blanks, then hit edit / delete / entire row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like
to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without
selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks

I don't know what the date of your post has to do with anything. However,
Office 2007 is definitely germaine because it has a completely different
user interface than previous verions. In 2007, do the following:

HomeEditingFind & SelectGo to Special...Blanks
HomeCellsDelete Sheet Rows

In the future, specify that you have 2007 right at the start, so the answers
can be tailored to your situation.

Regards,
Fred.

"Dael" wrote in message
...
BTW, I checked the date of this post... I am using office 2007.

"Ken Wright" wrote:

Select a column where you can be SURE that a blank cell means you want to
delete that row. Select the whole column, do edit / Go to / Special /
blanks, then hit edit / delete / entire row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"rgtest" wrote in message
...
I exported data from a Crystal Report into Excel. During the export
process
extra fields and and columns were added to the data set. I would like
to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without
selecting
each row? For example, could I run a script to delete rows with null
values
or space?

Thanks