98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Starting cell change in a Macro
Starting cell change in a Macro
Resolved · Low Priority · Version 2010
Mon 20 Nov 2017, 09:49Delegate Lewis said...
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.
Tue 21 Nov 2017, 12:55Trainer Wendy said...
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
Tue 21 Nov 2017, 13:14Delegate Lewis said...
RE: Starting cell change in a Macro
Hi Wendy
This was using the macro recorder within excel.
Thanks
Lewis
Tue 21 Nov 2017, 13:21Trainer Wendy said...
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
Tue 21 Nov 2017, 14:04Delegate Lewis said...
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
Mon 27 Nov 2017, 14:17Trainer Sarah said...
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
Fri 1 Dec 2017, 09:44Trainer Jens said...
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Applying and removing border from cell in Excel 2010Did you know the shortcut key for applying and removing the outline border for a cell? |
