Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro Help for a Novice
Firstly, I am a novice and therefore any instructions need to be idiot proof.
I have several spreadsheets that I receive daily. I seem to do the same routines on a daily basis and was therefore wondering if it can be automated. This is what I do on a daily basis: - Open them; Position on the second column and depress END and down arrow (to get me to the last row); Retype the value that is currently in that cell (i.e. if it say 578, I just retype it) and change the format of the cell from a date format (dd/mm/yy) to a general number. The reason I retype the value is that there is a formula in the cell; I then manually delete all the rows except the first and the last rows. I have tried to record a macro but it inserts a cell references and unfortunately this doesnt work as the range to be deleted changes on a daily basis. Can the above task(s) be automated so that I just run a macro? Any help offered would me appreciated. -- Thanks Ann |
#2
|
|||
|
|||
Add this to a standard code module.
Got to the VB IDE - ALt-F11 Insert a code module - InsertModule Paste this code Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Firstly, I am a novice and therefore any instructions need to be idiot proof. I have several spreadsheets that I receive daily. I seem to do the same routines on a daily basis and was therefore wondering if it can be automated. This is what I do on a daily basis: - Open them; Position on the second column and depress END and down arrow (to get me to the last row); Retype the value that is currently in that cell (i.e. if it say 578, I just retype it) and change the format of the cell from a date format (dd/mm/yy) to a general number. The reason I retype the value is that there is a formula in the cell; I then manually delete all the rows except the first and the last rows. I have tried to record a macro but it inserts a cell references and unfortunately this doesn't work as the range to be deleted changes on a daily basis. Can the above task(s) be automated so that I just run a macro? Any help offered would me appreciated. -- Thanks Ann |
#3
|
|||
|
|||
Bob,
Thanks for the simple instruction. The macro deletes the rows as requested, however, the value is blanked out (i.e. last rows cell B. Any ideas why this is happening? Thanks -- Thanks Ann "Bob Phillips" wrote: Add this to a standard code module. Got to the VB IDE - ALt-F11 Insert a code module - InsertModule Paste this code Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Firstly, I am a novice and therefore any instructions need to be idiot proof. I have several spreadsheets that I receive daily. I seem to do the same routines on a daily basis and was therefore wondering if it can be automated. This is what I do on a daily basis: - Open them; Position on the second column and depress END and down arrow (to get me to the last row); Retype the value that is currently in that cell (i.e. if it say 578, I just retype it) and change the format of the cell from a date format (dd/mm/yy) to a general number. The reason I retype the value is that there is a formula in the cell; I then manually delete all the rows except the first and the last rows. I have tried to record a macro but it inserts a cell references and unfortunately this doesn't work as the range to be deleted changes on a daily basis. Can the above task(s) be automated so that I just run a macro? Any help offered would me appreciated. -- Thanks Ann |
#4
|
|||
|
|||
Sorry, I dropped a dot. Try this
Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = .Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Bob, Thanks for the simple instruction. The macro deletes the rows as requested, however, the value is blanked out (i.e. last rows cell B. Any ideas why this is happening? Thanks -- Thanks Ann "Bob Phillips" wrote: Add this to a standard code module. Got to the VB IDE - ALt-F11 Insert a code module - InsertModule Paste this code Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Firstly, I am a novice and therefore any instructions need to be idiot proof. I have several spreadsheets that I receive daily. I seem to do the same routines on a daily basis and was therefore wondering if it can be automated. This is what I do on a daily basis: - Open them; Position on the second column and depress END and down arrow (to get me to the last row); Retype the value that is currently in that cell (i.e. if it say 578, I just retype it) and change the format of the cell from a date format (dd/mm/yy) to a general number. The reason I retype the value is that there is a formula in the cell; I then manually delete all the rows except the first and the last rows. I have tried to record a macro but it inserts a cell references and unfortunately this doesn't work as the range to be deleted changes on a daily basis. Can the above task(s) be automated so that I just run a macro? Any help offered would me appreciated. -- Thanks Ann |
#5
|
|||
|
|||
Bob,
Once again thanks. It works except that I have to manually reformat the cell B2 (after the delete) to Number Format - General. I recorded a macro and it gave :- Range("B2").Select Selection.NumberFormat = "General" I therefore added this to the macro you supplied and all is well. Thanks again for you invaluable help. Ann -- Thanks Ann "Bob Phillips" wrote: Sorry, I dropped a dot. Try this Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = .Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Bob, Thanks for the simple instruction. The macro deletes the rows as requested, however, the value is blanked out (i.e. last rows cell B. Any ideas why this is happening? Thanks -- Thanks Ann "Bob Phillips" wrote: Add this to a standard code module. Got to the VB IDE - ALt-F11 Insert a code module - InsertModule Paste this code Sub Reformat() Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Cells(iLastRow, "B") .Value = Value End With Rows("2:" & iLastRow - 1).Delete End Sub -- HTH Bob Phillips "Ann" wrote in message ... Firstly, I am a novice and therefore any instructions need to be idiot proof. I have several spreadsheets that I receive daily. I seem to do the same routines on a daily basis and was therefore wondering if it can be automated. This is what I do on a daily basis: - Open them; Position on the second column and depress END and down arrow (to get me to the last row); Retype the value that is currently in that cell (i.e. if it say 578, I just retype it) and change the format of the cell from a date format (dd/mm/yy) to a general number. The reason I retype the value is that there is a formula in the cell; I then manually delete all the rows except the first and the last rows. I have tried to record a macro but it inserts a cell references and unfortunately this doesn't work as the range to be deleted changes on a daily basis. Can the above task(s) be automated so that I just run a macro? Any help offered would me appreciated. -- Thanks Ann |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |