Variables in Excel (Cells & columns) - Excel 2003
 

Morning all,

I have 2 problems:

1) I am looking to define 2 cells. This is because I want to check in these
cells for all blanks and replace with zeros. No problem there. But instead
of putting the usual - range("a1","c14").select I need to put in variables as
the number of columns on this will change.

I can define a row or column and use this as a reference but am having
trouble defining a specific individual cell:

r = ActiveCell.Column
b = Activecell.row

etc....

How would I go about giving a cell a reference so this macro will work no
matter how many columns there are ?


2) After I do this I want to go through each of the relevant columns and do
a filter for all zeros. No problem in doing that if I know how many columns
there are but this is varable so how do I define this ? At present I have as
below:

Selection.AutoFilter field:=r + 1, Criteria1:="0"
Selection.AutoFilter field:=r + 2, Criteria1:="0"
Selection.AutoFilter field:=r + 3, Criteria1:="0"
etc...


This works fine until I get to the point of the final column. After this
obviously an error appears because there are no columns left.

How do I work variable into this ?


Any help I can get would be much appreciated !!

Thanks in advance

Variables in Excel (Cells & columns) - Excel 2003
 

Assuming headings/data in row 1, then last column is:

Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

HTH

"Coppercrutch" wrote:

Morning all,

I have 2 problems:

1) I am looking to define 2 cells. This is because I want to check in these
cells for all blanks and replace with zeros. No problem there. But instead
of putting the usual - range("a1","c14").select I need to put in variables as
the number of columns on this will change.

I can define a row or column and use this as a reference but am having
trouble defining a specific individual cell:

r = ActiveCell.Column
b = Activecell.row

etc....

How would I go about giving a cell a reference so this macro will work no
matter how many columns there are ?


2) After I do this I want to go through each of the relevant columns and do
a filter for all zeros. No problem in doing that if I know how many columns
there are but this is varable so how do I define this ? At present I have as
below:

Selection.AutoFilter field:=r + 1, Criteria1:="0"
Selection.AutoFilter field:=r + 2, Criteria1:="0"
Selection.AutoFilter field:=r + 3, Criteria1:="0"
etc...


This works fine until I get to the point of the final column. After this
obviously an error appears because there are no columns left.

How do I work variable into this ?


Any help I can get would be much appreciated !!

Thanks in advance