numbers and macros
 

I have a couple of questions regarding managing the enormous amount of data I
see everyday that I simply cannot find a way to execute it in an excel Macro.

Basically my macros end on the block of my recorded macro how do make
encompass the whole column or sheet as the speadsheets I consoldate become
larger?

Secondly, I have to add a zero in front of every number (under 10) in a
specific column only. the only way i do this now is by changing the column
to text an maually copy paste.

numbers and macros
 

1. You can get the last filled column or row using code

'Last row filled in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Last filled column in Row1
lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

2. Zero in front..If this is for the display you can achieve this using code
itself by changing the number format.

If this post helps click Yes
---------------
Jacob Skaria


"Evil with a K" wrote:

I have a couple of questions regarding managing the enormous amount of data I
see everyday that I simply cannot find a way to execute it in an excel Macro.

Basically my macros end on the block of my recorded macro how do make
encompass the whole column or sheet as the speadsheets I consoldate become
larger?

Secondly, I have to add a zero in front of every number (under 10) in a
specific column only. the only way i do this now is by changing the column
to text an maually copy paste.

numbers and macros
 

Jacob,
Thanks the last row/colomn advice worked like it had eyes. The other one
woked best when I switched the info to text and hit custom then placed 2
zeros in.

I may sound ungrateful, but there is one more issue I have that I just can't
get right. And that is I have a column that displays a date if no date exists
then a zero is placed in it. I know the IF Function could do it, but when I
use this formula =IF(0,null,"") it removes all dates. What I am trying to do
is to clear (display blank cell)that have a value of zero (0) but keep the
date in all cells that have a date. (I am aware of the fomula being in the
blank cell but I already know how to fix that)

Any help would be appreciated.
thx
Rick

"Jacob Skaria" wrote:

1. You can get the last filled column or row using code

'Last row filled in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Last filled column in Row1
lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

2. Zero in front..If this is for the display you can achieve this using code
itself by changing the number format.

If this post helps click Yes
---------------
Jacob Skaria


"Evil with a K" wrote:

I have a couple of questions regarding managing the enormous amount of data I
see everyday that I simply cannot find a way to execute it in an excel Macro.

Basically my macros end on the block of my recorded macro how do make
encompass the whole column or sheet as the speadsheets I consoldate become
larger?

Secondly, I have to add a zero in front of every number (under 10) in a
specific column only. the only way i do this now is by changing the column
to text an maually copy paste.

numbers and macros
 

You can handle that within the formula itself. Let me know the formula that
gets that date or 0.

If this post helps click Yes
---------------
Jacob Skaria


"Evil with a K" wrote:

Jacob,
Thanks the last row/colomn advice worked like it had eyes. The other one
woked best when I switched the info to text and hit custom then placed 2
zeros in.

I may sound ungrateful, but there is one more issue I have that I just can't
get right. And that is I have a column that displays a date if no date exists
then a zero is placed in it. I know the IF Function could do it, but when I
use this formula =IF(0,null,"") it removes all dates. What I am trying to do
is to clear (display blank cell)that have a value of zero (0) but keep the
date in all cells that have a date. (I am aware of the fomula being in the
blank cell but I already know how to fix that)

Any help would be appreciated.
thx
Rick

"Jacob Skaria" wrote:

1. You can get the last filled column or row using code

'Last row filled in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Last filled column in Row1
lngLastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

2. Zero in front..If this is for the display you can achieve this using code
itself by changing the number format.

If this post helps click Yes
---------------
Jacob Skaria


"Evil with a K" wrote:

I have a couple of questions regarding managing the enormous amount of data I
see everyday that I simply cannot find a way to execute it in an excel Macro.

Basically my macros end on the block of my recorded macro how do make
encompass the whole column or sheet as the speadsheets I consoldate become
larger?

Secondly, I have to add a zero in front of every number (under 10) in a
specific column only. the only way i do this now is by changing the column
to text an maually copy paste.