Manipulate the appearance of a spreadsheet in VBA

[CaptainGravyBum]

Hello,
Hope you can help. I have data which has to be copied into Excel for an import and I need to find a way to manipulate the sheet to show only the required data in each row.
The number of rows will change every time this is pasted in and I need the Customer Code shown in B1 and B5 to appear to the left of each Con No row. The header rows need to be removed as well if possible, because I can add them in at the top if needed. Customer ref should always be in column B, but if copied incorrectly could appear in Column A, so something to cover both eventualities would be beneficial.
Oh, and when the row containing the Customer Code has been used, it can also be removed.

I'm not sure how to write a macro to find the cells with customer ref and insert the information on the rows where it is needed.

Sorry it's just an image, my company policies do not allow the XL2BB Add-in to run.

 

[mumps]

It is hard to work with pictures. Could you try copying the range and pasting it here? What do you mean by "Customer ref"? It would also be helpful if you could copy/paste two samples, one showing the Customer ref in column A and the other showing it in column B. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[CaptainGravyBum]

It is hard to work with pictures. Could you try copying the range and pasting it here? What do you mean by "Customer ref"? It would also be helpful if you could copy/paste two samples, one showing the Customer ref in column A and the other showing it in column B. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Hi @mumps,
I've uploaded a link to the sheet Test Data.
Sorry, I didn't explain the customer ref field, on the sheet that is indicated by the yellow cells. The first customer ref is in column A and the second is in column B, this is due to it being copied from a source that sometimes has an image in the first column on that row so it pushes the customer ref over to column B.
When the customer ref is to the left of the Con No column data, that row is no longer required and neither are the header rows highlighted in grey (Rows 1,2, 4&5 on this example).
Regards,

 

[mumps]

Try:

Sub ArrangeData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, x As Long
    Columns("A:A").Insert Shift:=xlToRight
    v = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
    For i = LBound(v) To UBound(v)
        If v(i, 1) = "" Then
           Range("C" & i).Cut Range("B" & i)
        End If
    Next i
    With Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
        For i = 1 To .Areas.Count
            frow = .Areas(i).Cells(1).Row
            lrow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Range("A" & frow + 1 & ":A" & lrow) = Range("B" & frow - 1)
        Next i
    End With
    Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Borders.LineStyle = xlContinuous
    Application.ScreenUpdating = True
End Sub

 

[CaptainGravyBum]

Thanks for this, it is giving me an error when I run it, the highlighted text is where it stops

Sub ArrangeData()
Application.ScreenUpdating = False
Dim v As Variant, i As Long, x As Long
Columns("A:A").Insert Shift:=xlToRight
v = Range("B1", Range("B" & Rows.Count).End(xlUp)).Value
For i = LBound(v) To UBound(v)
If v(i, 1) = "" Then
Range("C" & i).Cut Range("B" & i)
End If
Next i
With Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
For i = 1 To .Areas.Count
frow = .Areas(i).Cells(1).Row
lrow = .Areas(i).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & frow + 1 & ":A" & lrow) = Range("B" & frow - 1)
Next i
End With
Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
Range("A1", Range("A" & Rows.Count).End(xlUp)).Borders.LineStyle = xlContinuous
Application.ScreenUpdating = True
End Sub

 

[CaptainGravyBum]

Actually, scratch that. I think it was because I'd pasted the formatting in accidentally, it seems to work ok with just paste values.

 

[mumps]

Glad it worked out.

 

[CaptainGravyBum]

Glad it worked out.

Yep, worked a treat, I just had to make sure the destination cells were cleared first and only values pasted.
Thanks for your help

 

[mumps]

You are very welcome.

 

[CaptainGravyBum]

Hi @mumps,
Would you be able to help me with a small issue I'm having with this code?
If I copy the source data into the sheet directly (from a web page) and paste using match destination formatting, the code runs fine.
However, if I copy the same data from a spreadsheet or from the same source and use the paste values only option, it will give an error message shown below:

This is where it breaks.

Like I say, it works fine if pasted in a specific way, but as this process needs to be followed by different users, I need to make sure it is bulletproof.