Macro

aftershokk · May 14th, 2002, 07:35 AM

I need a macro that will delete balnk rows for a selected range that varies each time I receive the document.

Help!

crimlet · May 14th, 2002, 07:41 AM

Will for example column A always be blank, of will there sometimes be nothing in A but something in B ect.. If this is the case set your Macro recorder to On and record yourself using the filter comand and select columns A as non blanks copy then paste to desired worksheet

aftershokk · May 14th, 2002, 08:01 AM

How do you get non-blanks to filter.
I cannot get that option on my filter command?
thanks,

crimlet · May 14th, 2002, 08:15 AM

If you select the row with your headings and choose, autofilter, you should then see the filter tabs. If you then click on the one with blank cells e.g. column A, scroll all the way to the bottom of the selectable variables you should be presented with the options to show Blanks & Non Blanks

crimlet · May 14th, 2002, 08:20 AM

try using the following code in a macro
sheets("put your sheet name here").select
Rows("1:1").Select 'this should be the heading of you sheet
Selection.AutoFilter
Selection.AutoFilter Field:=1,'this will be colums A as discuss before
Criteria1:="="
Rows("6:101").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1

aftershokk · May 14th, 2002, 08:27 AM

thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 07:35 AM	#1
aftershokk New Member Join Date: May 2002 Location: Brian Posts: 37	I need a macro that will delete balnk rows for a selected range that varies each time I receive the document. Help!

May 14th, 2002, 07:41 AM	#2
crimlet Board Regular Join Date: May 2002 Location: Ipswich, Suffolk, England Posts: 135	Will for example column A always be blank, of will there sometimes be nothing in A but something in B ect.. If this is the case set your Macro recorder to On and record yourself using the filter comand and select columns A as non blanks copy then paste to desired worksheet

May 14th, 2002, 08:01 AM	#3
aftershokk New Member Join Date: May 2002 Location: Brian Posts: 37	How do you get non-blanks to filter. I cannot get that option on my filter command? thanks,

May 14th, 2002, 08:15 AM	#4
crimlet Board Regular Join Date: May 2002 Location: Ipswich, Suffolk, England Posts: 135	If you select the row with your headings and choose, autofilter, you should then see the filter tabs. If you then click on the one with blank cells e.g. column A, scroll all the way to the bottom of the selectable variables you should be presented with the options to show Blanks & Non Blanks

May 14th, 2002, 08:20 AM	#5
crimlet Board Regular Join Date: May 2002 Location: Ipswich, Suffolk, England Posts: 135	try using the following code in a macro sheets("put your sheet name here").select Rows("1:1").Select 'this should be the heading of you sheet Selection.AutoFilter Selection.AutoFilter Field:=1,'this will be colums A as discuss before Criteria1:="=" Rows("6:101").Select Selection.Delete Shift:=xlUp Selection.AutoFilter Field:=1

May 14th, 2002, 08:27 AM	#6
aftershokk New Member Join Date: May 2002 Location: Brian Posts: 37	thanks