Formula or VBA to align multiple cells with same value under one column

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,I am a novice at excel and would appreciate any help I can get.

I would like to know is there a function/formula/VBA that one can use to align multiple cells with the same value across a worksheet into a specific column for for example:

I want to align all the cells highlighted in red with the value 3026 (Specific code) under column CH by inserting blank cells in front of 3026. It is very important that the 3026 values stay in its original row. Is there any other way of doing this without manually having to insert blank cells row by row so that all the 3026 values line up under column CH?
Excel Query.jpg
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,497
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

See if this does what you want:
VBA Code:
Sub MyInsertCells()

    Dim r As Long
    Dim rng As Range
    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all rows starting in row 4
    For r = 4 To Cells(Rows.Count, "CH").End(xlUp).Row
'       Find location of "3026"
        Set rng = Rows(r).Find(What:="3026", LookAt:=xlWhole)
'       If found, insert blank cells, if needed
        If Not (rng Is Nothing) Then
            c = rng.Column
'           See if column found is before column CH
            If c < 86 Then Range(Cells(r, c), Cells(r, 85)).Insert Shift:=xlToRight
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

See if this does what you want:
VBA Code:
Sub MyInsertCells()

    Dim r As Long
    Dim rng As Range
    Dim c As Long
   
    Application.ScreenUpdating = False
   
'   Loop through all rows starting in row 4
    For r = 4 To Cells(Rows.Count, "CH").End(xlUp).Row
'       Find location of "3026"
        Set rng = Rows(r).Find(What:="3026", LookAt:=xlWhole)
'       If found, insert blank cells, if needed
        If Not (rng Is Nothing) Then
            c = rng.Column
'           See if column found is before column CH
            If c < 86 Then Range(Cells(r, c), Cells(r, 85)).Insert Shift:=xlToRight
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub
Hi Joe4 thanks, I am glad I joined the board should have done it a long time ago.

Thank you very much for the VBA code it works perfectly! Really appreciate it very much.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,497
Office Version
  1. 365
Platform
  1. Windows
You are welcome!
Glad to hear that it all worked for you.
:)
 

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are welcome!
Glad to hear that it all worked for you.
:)
Hi Joe4 I hope that you are well!

I have been using the VBA code for a couple of days now and it works perfectly on smaller sheets.

Today I used it on a larger sheet with 2000+ rows and it only runs upto row 157 for some or other reason (All the other sheets have been less that 157 rows of data). Am I doing something wrong or not inputting the ranges or cell numbers in correctly?

Your help will be much appreciated :)
 

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Joe4 I hope that you are well!

I have been using the VBA code for a couple of days now and it works perfectly on smaller sheets.

Today I used it on a larger sheet with 2000+ rows and it only runs upto row 157 for some or other reason (All the other sheets have been less that 157 rows of data). Am I doing something wrong or not inputting the ranges or cell numbers in correctly?

Your help will be much appreciated :)
I think I have figured out. I inserted the VBA incorrectly and did not run correctly...I am such a noob!

Hope you are well though :)
 

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think I have figured out. I inserted the VBA incorrectly and did not run correctly...I am such a noob!

Hope you are well though :)
Nope it doesn't work :/ it just runs upto a certain row number then stops sorry to be such a pest.

Please help
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,497
Office Version
  1. 365
Platform
  1. Windows
Are you sure that column CH is always populated, or is it sometimes blank for rows with data?

I am willing to bet that as long as you have not messed with the code, it is probably a data issue. But without seeing a sample file, it is difficult to debug.
If you can upload a sample file that is not working to a file sharing site and provide the link here, I can look at it and see if I can determine what is going on.
If you have changed the VBA code any, please post the code you are using here.
 

RoanRSA

New Member
Joined
Nov 3, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Are you sure that column CH is always populated, or is it sometimes blank for rows with data?

I am willing to bet that as long as you have not messed with the code, it is probably a data issue. But without seeing a sample file, it is difficult to debug.
If you can upload a sample file that is not working to a file sharing site and provide the link here, I can look at it and see if I can determine what is going on.
If you have changed the VBA code any, please post the code you are using here.
Column CH is not always populated with data it is blank sometimes which causes the row length to vary.

Aaahaaa! I saw now that if I make sure that there is something in all the cells under column CH that are empty the code works perfectly fine for the whole sheet in every single row. So basically there must be some form of data in the destination column (Column CH) for the code to work?

Nope I haven't messed with the code at all :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,497
Office Version
  1. 365
Platform
  1. Windows
So basically there must be some form of data in the destination column (Column CH) for the code to work?
Not necessarily. We just need to adjust the part of the loop where we tell it to end, specifically this line:
Rich (BB code):
For r = 4 To Cells(Rows.Count, "CH").End(xlUp).Row

Pick some other column that will ALWAYS have data, and use that instead. Let's say it is column A, then change the code to this:
Rich (BB code):
For r = 4 To Cells(Rows.Count, "A").End(xlUp).Row

Note that this doesn't tell it what column to adjust, it is just telling it which column we can look at to determine where our last row of data occurs.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,084
Messages
5,576,022
Members
412,694
Latest member
Deaf1Too
Top