Need VBA out- put to different location on worksheet.

[estatefinds]

I have data that is placed in columns ABCDE and need this data to be moved into Columns BCDEF which will then will have to move the macro Sub M_snb() which it look at the the data and what ever is uncolored currently result in columns GHIJK row 46. so I need the VBA to results, instead to be placed in columns AL AM AN AO AP ROW 9; this will be its permanent position. I had attached file. Thank you!

p.s. also the data in the columns ABCDE that will be placed in to Columns BCDEF will change meaning data will be added so the macro has to be able follow where the uncolored values are any where in the columns. the attachment gives a clear picture. please,
Thank you!

[PAB]

Hi Dennis,

Change the Macro to this...

Sub M_snb()
    sn = Cells(9, 2).CurrentRegion
     
    For j = 1 To UBound(sn)
        For jj = 1 To UBound(sn, 2)
            If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
            c00 = c00 & " " & sn(j, jj) & " "
        Next
    Next
     
    For j = 1 To UBound(sn)
        sp = Filter(Application.Index(sn, j), "~", 0)
        For jj = 1 To UBound(sn, 2)
            sn(j, jj) = ""
            If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
        Next
        If UBound(sp) > -1 Then c01 = c01 & " " & j
    Next
    sp = Application.Transpose(Split(Trim(c01)))
     
    Cells(45, 7).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub

I hope this helps!

[estatefinds]

thank you!, ok so I moved the DATA from ABCDE to BCDEF now I need the results of the data instead of placed into GHIJK starting on row 46 instead placed to the Columns AL AM AN AO AP on row 11. also can thus macro be made so where ever the data is in this columns the Macro will still run? I ask cause more data will be added and the unfilled numbers will change. please,
Thank you

[PAB]

Hi Dennis,

Give this a go...

Sub M_snb()
    sn = Cells(9, 2).CurrentRegion
     
    For j = 1 To UBound(sn)
        For jj = 1 To UBound(sn, 2)
            If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
            c00 = c00 & " " & sn(j, jj) & " "
        Next
    Next
     
    For j = 1 To UBound(sn)
        sp = Filter(Application.Index(sn, j), "~", 0)
        For jj = 1 To UBound(sn, 2)
            sn(j, jj) = ""
            If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
        Next
        If UBound(sp) > -1 Then c01 = c01 & " " & j
    Next
    sp = Application.Transpose(Split(Trim(c01)))
     
    Cells(11, 38).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub

I hope this helps!

[estatefinds]

Thank you! it is now where it needs to be Thank you!!!!

[PAB]

You're welcome, thanks for the feedback.

[estatefinds]

i am having trouble with this it is taking the numbers in column A and placing with data that is only supposed to be data in columns BCDEF can you help me fix this? please?

[estatefinds]

i dont understand what happend?

[estatefinds]

I got it working again Thanks

[estatefinds]

okay i need help this is not working the way its supposed to.

the only data is supposed to be in columns AL:AP are from BCDEF. the column A gets left alone is that is used for another macro. Please help me Correct this!
Thank you

[estatefinds]

can this code be re written to avoid the data in column A? reason is that data in column A is what is used for my highlighting if data in BCDEF. but when i run the macro Sub M_snb() sn = Cells(9, 2).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To UBound(sn, 2)
If InStr(c00, " " & sn(j, jj) & " ") <> 0 Then sn(j, jj) = "~"
c00 = c00 & " " & sn(j, jj) & " "
Next
Next

For j = 1 To UBound(sn)
sp = Filter(Application.Index(sn, j), "~", 0)
For jj = 1 To UBound(sn, 2)
sn(j, jj) = ""
If jj - 1 <= UBound(sp) Then sn(j, jj) = sp(jj - 1)
Next
If UBound(sp) > -1 Then c01 = c01 & " " & j
Next
sp = Application.Transpose(Split(Trim(c01)))

Cells(11, 38).Resize(UBound(sp), UBound(sn, 2)) = Application.Index(sn, sp, Array(1, 2, 3, 4, 5))
End Sub
it picks up the data in column A which if the code is written in a way to ignore the column A. Please!!!
Thank you!