I am trying to make, and or modify, a macro, to select the next cells down, after merging cells in a row. If I record a macro that moves and selects cells in the next row down, ready for the next Merge command, it will always send me to the same cells each time, EI instead of moving to a new set of 'next cells down'. I have tried to edit the macro, using 'ActiveCell.Offset(1,0).Select' but it is not working for me. Any suggestions?
Select the next cells down, after merging cells.
- Thread is marked as Resolved.
Merging Cells is a recipe for disaster and should be avoided.
We cannot help with code if we don't see it
Thank you for replying RoyUK. I appreciate your advice to avoid merging cell, but I am not using them in a part of the spreadsheet where calculations are to take place. IE they are in a place where I have name, address, type stuff. Having said that, following is the contents of the macro that combines the instruction to merge, and align to the left, with an indent. This is to help speed up the process of formatting the appearance of the spreadsheet.
What I am having trouble with is trying to add code to move the focus down to the equivalent cells on the next row down. As mentioned, I have tried to add a line with 'ActiveCell.Offset(1,0).Select' , but it is not working as expected. Can you assist?Code
Sub MergeAndLHAlign() ' ' MergeAndLHAlign Macro ' Merge cells and align Left ' ' Keyboard Shortcut: Ctrl+Shift+L With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 1 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With End Sub
I would still advise not to use MergedCells, I would have each line of an address on it's own row.
Also, it should not require selecting but try thisCode
Option Explicit Sub MergeAndLHAlign() Dim lRws As Long ' ' MergeAndLHAlign Macro ' Merge cells and align Left ' ' Keyboard Shortcut: Ctrl+Shift+L With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 1 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.Offset(1).Select End Sub
Look at this link to understand Roy's aversion to merged cells.
Thank you, Roy. I've entered the code as you show, and when executing, I get the message
I notice that the line "Dim 1Rws As Long" shows in red.
Also, the first line "Option Explicit" insists on placing itself above what I presume is the horizontal dividing line between the code of this macro and the one above it. Hmmm. Should the line "Option Explicit" be at the very top of our code?
So I have tried placing the lines "Option Explicit" and "Dim 1Rws As Long" below the line "Sub MergeAndLHAlign()". Now I get the message "Invalid inside proceedure". The code now is as follows:Code
Sub MergeAndLHAlign() (This line is highlighted yellow) ' ' MergeAndLHAlign Macro ' Merge cells and align Left ' Keyboard Shortcut: Ctrl+Shift+L ' Option Explicit Dim 1Rws As Long (This text is red) With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 1 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Selection.Offset(1).Select End Sub
Your post does not comply with our Forum RULES. Use code tags around code.
Posting code between [ CODE] [ /CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. Please take a minute to read the few rules you agreed to when you joined.
(This thread should receive no further responses until this moderation request is fulfilled)
Sincerely my apologies. Happy to comply, but please, where is this 'Edit' to click on?
You cannot edit, so I have added code tags.
Any code above Option Explicit should not be there. Either remove it or move it below Option Explicit
Thank you, Roy. It is working now. I put it into a new module, and now I can run it via Alt+F8 and selecting and running the macro. So that is definitely progress.
I cannot get it to run via the keyboard shortcut though. I went into Module 1 and deleted the existing macro that used the same keyboard shortcut, but that hasn't helped.
Thank you for your help to date. BTW, what educational resource can you suggest for me to study to become proficient at VBA. There are a lot out there of course.
NOTE: I cannot get anywhere with cryptic crosswords. Is it possible that my brain is not wired right to grasp VBA? Or can anyone do it, with enough effort and time?
I deleted everything in Module 1, and pasted your code in, and it works via Alt+F8 and selecting and running the macro. Still won't work via keyboard shortcut. Now i have the code in both Module 1 and 2, and they both work as described above.
You don't need the code in two modules.
I'll have a look at the short cut.
The way I learn is just practise, look at others' code and try to understand.
Online guides are generally helpful but not always.
You are it doesn't work with the short cut. I'm not sure why, I'll look into it when I get chance
Don’t have an account yet? Register yourself now and be a part of our community!