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

[RoanRSA]

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?

 

[Joe4]

Welcome to the Board!

See if this does what you want:

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]

Welcome to the Board!

See if this does what you want:

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]

You are welcome!
Glad to hear that it all worked for you.

 

[RoanRSA]

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]

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]

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]

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]

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]

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:

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:

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.