Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns A, B, C , D & E won't unhide in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 - Protecting and Hiding Columns | Excel Discussion (Misc queries) | |||
filter rows rather than columns in Excel 2003? | Excel Discussion (Misc queries) | |||
Columns in excel 2003 | Excel Worksheet Functions | |||
How do I count two variables in different columns in excel? | Excel Worksheet Functions |