Variable range and deleting empty rows
 

Hi,

I have a spreadsheet that i run a macro on to trim it down into a table and
then paste it into Word. I would like to be able to do two things: -

1. How would i set the range as a variable so that, should the range change
in future, the VBA script will not display any errors?

2. I would like to be able to delete or hide rows that do not have any data
(based on the variable range in question 1). Not all of the columns have data
so i cannot sort this by using a filter.

The more complete code, the better as i am a learning novice.

Thank you in advance,
Paul.

--
Message posted via http://www.officekb.com

Variable range and deleting empty rows
 

Hi Paul,

The following macro deletes the entire row if the cell in Column A is empty.
Sub DeleteBlanks()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub
You can change the column designation to whatever other column you prefer to use for the test.

If you need something more sophisticated, post back with the details.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"par4724 via OfficeKB.com" <u41743@uwe wrote in message news:8231b10316203@uwe...
Hi,

I have a spreadsheet that i run a macro on to trim it down into a table and
then paste it into Word. I would like to be able to do two things: -

1. How would i set the range as a variable so that, should the range change
in future, the VBA script will not display any errors?

2. I would like to be able to delete or hide rows that do not have any data
(based on the variable range in question 1). Not all of the columns have data
so i cannot sort this by using a filter.

The more complete code, the better as i am a learning novice.

Thank you in advance,
Paul.

--
Message posted via http://www.officekb.com

Variable range and deleting empty rows
 

Might want to check this page first:

http://www.rondebruin.nl/specialcells.htm


--JP


On Apr 4, 8:45*pm, "macropod" wrote:
Hi Paul,

The following macro deletes the entire row if the cell in Column A is empty.
Sub DeleteBlanks()
*Range("A:A").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
End Sub
You can change the column designation to whatever other column you prefer to use for the test.

If you need something more sophisticated, post back with the details.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Variable range and deleting empty rows
 

Thanks JP,

I wasn't aware of that.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"JP" wrote in message ...
Might want to check this page first:

http://www.rondebruin.nl/specialcells.htm


--JP


On Apr 4, 8:45 pm, "macropod" wrote:
Hi Paul,

The following macro deletes the entire row if the cell in Column A is empty.
Sub DeleteBlanks()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub
You can change the column designation to whatever other column you prefer to use for the test.

If you need something more sophisticated, post back with the details.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Variable range and deleting empty rows
 

macropod wrote:
Thanks JP,

I wasn't aware of that.

Cheers
Might want to check this page first:

http://www.rondebruin.nl/specialcells.htm

--JP

On Apr 4, 8:45 pm, "macropod" wrote:
Hi Paul,

[quoted text clipped - 11 lines]
[MVP - Microsoft Word]
-------------------------


Hi, sorry for the late post. Thank you for your replies. This proved most
helpful and worked great.

Thanks again and regards,
Paul.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200804/1