Select the next cells down, after merging cells.

  • 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?

  • 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?

    Edited 2 times, last by royUK: Add code tags ().

  • 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 this

  • dejayajay

    Look at this link to understand Roy's aversion to merged cells.

    External Content
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

  • Thank you, Roy. I've entered the code as you show, and when executing, I get the message

    "'Compile Error:

    Syntax Error"

    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:

    Edited once, last by royUK: add code tags ().

  • Code Tags

    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)

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!