Macro Help for a Novice

[Guest]

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.

 

[Bob Phillips]

Add this to a standard code module.

Got to the VB IDE - ALt-F11
Insert a code module - Insert>Module
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

 

[Guest]

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

Add this to a standard code module.

Got to the VB IDE - ALt-F11
Insert a code module - Insert>Module
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

 

[Bob Phillips]

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

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

 

[Guest]

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