Convert bad SAP report into nice table

[Krisz06]

Hello everyone,
I need some help. I'm trying to create a macro which converts a (really bad) SAP report to a good table with only the necessary info.
The table is about vendors. Till the last step everything works correctly.
But there is a little problem, the report contains the existing data from the system and in some case there are an extra line ("street 2" field). That ruins the macro logic as the last step says "ActiveCell.Offset(1, -10).Range("A1:A71").Select"
In some case it should be 72 instead of 71.

Here is the code:

Sub XYZ ()


Range("C9").Select


Do
If Selection.Value = "" Then Exit Do


ActiveCell.Offset(6, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(-6, 1).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(6, 7).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-6, -6).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(13, -2).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-13, 3).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(13, 5).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-13, -4).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(17, -4).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-17, 5).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(26, 3).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-26, -2).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(42, -6).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-42, 7).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(11, 1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-11, 0).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(64, -8).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-64, 9).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(65, -9).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-65, 10).Range("A1").Select
ActiveSheet.Paste


ActiveCell.Offset(1, -10).Range("A1:A71").Select
Selection.EntireRow.Delete
ActiveCell.Select


Loop


End Sub

I have been thinking on 2 possible solution:
Somehow using "Selection.End(xlDown).Select".. with this a little problem that there is an empty cell in that columnThe other option is to remove every line which contain "Street2" in the column B.. that would make all separate vendor has only 71 line.




Can someone please help?
Thank you!

 

[Fluff]

Glad to help & thanks for the feedback