Creating a Macro to Find Text and Then Merge Cells [Solved/Closed]

Report
Posts
4
Registration date
Monday May 19, 2014
Status
Member
Last seen
May 28, 2014
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
Hi-

I need some help with creating a macro. I haven't been able to get it to record correctly and I am completely illiterate in Visual Basic.

So what I need the macro to do is to find specific text, select the cell with that text, and then merge it with the 3 blank cells to the right of it (so that the footer isn't mushed into column A, but spread out on one line). There are 3 seperate lines of footers I need this to happen with. I've tried recording it but using CTRL F and finding the cell that way doesn't seem to stick in the macro (the macro just selects the same line as before when I try to run it in a different report).

The reason I need it to find the cell with the text first is that these are footers, and are in hundreds of reports that I need to re-format, and in each report the footers are on different rows, so I can't just create the macro to go to a specific row since it changes from report to report.


Any help you can give is greatly appreciated!!

Thanks,
Katie

4 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Hi Katie,

It clear to me what you want to do but unclear why.

1) A footer is always placed at the bottom of a document, which means a fixed position.
2) If the 3 cells to the right are empty, then the text in the first cell will be visible.

Anyway, the following code will go through column A looking for the 3 phrases and merging the next 3 cells to the right of the phrase found:
Sub RunMe()
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow)
    If cell = "Other grades include" _
    Or cell = "Data reflect grades posted as of" _
    Or cell = "Report produced by" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Merge
    End If
Next cell
End Sub


Best regards,
Trowa
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Monday May 19, 2014
Status
Member
Last seen
May 28, 2014

Hi Trowa- that worked! Thank you!

In regards to your 2 comments, I have hundreds of reports where these footers are at the bottom of the document, but the specific row changes as each report is a different length. And when the report is created (run from an automated program) the footer text doesn't span the columns, it just jams up into the cell in column A (the text wraps) so wanted to get it to spread out.

Thanks so much again for your help- I greatly appreciate it!

Katie
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Thanks for the clarification Katie and I am glad to be able to help you.

See you
Posts
4
Registration date
Monday May 19, 2014
Status
Member
Last seen
May 28, 2014

Hi-

I was wondering if you could help me modify this code slightly. Is there a way to say 'contains' instead of equals when the macro is looking for the text? The text of the 3 different cells changes ever so slightly every few months so if I could say if the cell contains "other grades include" then do the merge, it would save me from having to update the code every few months.

Thanks for your help!!

Katie
Posts
4
Registration date
Monday May 19, 2014
Status
Member
Last seen
May 28, 2014

Okay nevermind- I posted this on another forum as well and got an answer (to change the equals to 'like' and add wildcards * in my text)

This is such a huge help though- thanks so much for helping me through this!

Katie
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
Thanks Katie for coming back to post your answer.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!