Lewis has attended:
Excel Intermediate course
Excel Advanced course
Project Advanced course
Starting cell change in a Macro
I have a macro I want to run where the initial cell changes, the rows stay the same but the column doesn't. This is so I can copy and paste data straight in, and then use a macro to clean up and clear the copied data.
The macro is as:
Sub Column_Clean_Up()
'
' Column_Clean_Up Macro
' A Macro that cleans up the hours in the column, so the result is only hours left in the appropriate row.
'
'
Range("D4:G4").Select
Selection.ClearContents
Range("D6:G8").Select
Selection.ClearContents
Range("D10:G12").Select
Selection.ClearContents
Range("D14:G16").Select
Selection.ClearContents
End Sub
How do I make it so the macro uses where I click my mouse as the starting point and works from there, basically as a calibrated start point.
This starting cell will change weekly.
RE: Starting cell change in a Macro
Hi Lewis
Thank you for using the forum
Has this been manually programmed or have you used the macro recorder?
Thanks
Wendy
RE: Starting cell change in a Macro
Hi Wendy
This was using the macro recorder within excel.
Thanks
Lewis
RE: Starting cell change in a Macro
Hi Lewis
Are you selecting cells with the mouse or using keyboard shortcuts?
When using the mouse to select cells, the macro will remember the range. Using a keyboard shortcut (Ctrl + Shift + Arrow Key Down), Excel looks for the last cell containing data and selects it. Then press delete or one of the clear contents options.
This should then run the macro from whatever starting point you select.
Your macro would read something like this:
Sub Column_Clean_Up()
' Column_Clean_Up Macro
' A Macro that cleans up the hours in the column, so the result is only hours left in the appropriate row.
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Sub
Hope this helps.
Kind regards
Wendy
RE: Starting cell change in a Macro
Hi Wendy
The problem is, I want to delete some rows and leave others within the same table.
As you can see from my original post, I want to delete D6:G8, but then leave the row D9 to G9.
This is because I have multiple entries that are repeated, thus I want to keep one complete row, and delete the entries in 3 of the rows, which are duplicates.
Am I making sense? Is this possible?
Thanks
Lewis
RE: Starting cell change in a Macro
Hi Lewis
Sorry for the slow reply, Wendy has been extremely busy training all week. She will definitely get back to you by the end of the week, but hopefully one of our other VBA trainers should be able to reply with some ideas even sooner.
Hope this is ok.
Regards,
Sarah
Excel Trainer
RE: Starting cell change in a Macro
Hi Lewis,
I am sorry for the late answer.
The code below can do the job.
before you run the code you will have to select the right cell. If you want to start from column D you will have to select D1 before you run the code. If you want to start from column M you must select M1 before you run the code.
Please let me know if it doesn't do what you expect.
Sub DeleteSpecificRows()
Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(3, 3)).ClearContents
Range(ActiveCell.Offset(5, 0), ActiveCell.Offset(7, 3)).ClearContents
Range(ActiveCell.Offset(9, 0), ActiveCell.Offset(11, 3)).ClearContents
Range(ActiveCell.Offset(13, 0), ActiveCell.Offset(13, 3)).ClearContents
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
