Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Exporting Time Formatted Numbers inside Macros | Excel Worksheet Functions | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) |