Excel VBA cut, paste, delete process.

[andy_j]

Hi there,

I am relatively new to VBA and i have come across something that i hope is simple however i have got totally stuck on .

I want to cut and paste the data which appears as in table 1 to appear as in table 2 shown below. This includes deleting the two blank rows which will be created.

((28264) CAMARA)			Data	Data
ghost
popstick
((92491) App)			Data	Data
sole
heel
((96742) New)			Data	Data

Table1

((28264) CAMARA)	ghost	popstick	Data	Data
((92491) App)	sole	heel	Data	Data
((96742) New)	Shoe	Chair	Data	Data

Table 2

I have managed to do this once with the following macro, however, obviously this wont work its way through all of the data ending when the entire row is blank.

Sub move_2()
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Rows("2").Select
Selection.Delete Shift:=x1Up
Range("A2").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Rows("2").Select
Selection.Delete Shift:=x1Up
End Sub

If someone could help it would be most appreciated.

Thanks,

Andy.

[mdmackillop]

One way

Sub Test()
Dim r As Range, cel As Range
Set r = Cells(1, 1).CurrentRegion
For Each cel In r
If Left(cel, 2) = "((" Then
cel.Offset(1).Cut cel.Offset(, 1)
cel.Offset(2).Cut cel.Offset(, 2)
End If
Next
r.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[Paul_Hossler]

Hi Andy --

If you use the [#] icon it will insert [ CODE ] tags and you can paste your macro(s) between them

It helps set the code off and does a little formatting



Also, you can browse the FAQs http://www.vbaexpress.com/forum/faq.php to learn more about the forum and some important rules

[andy_j]

spot on!

the delete line didn't want to work for some reason, so i substituted the below and it works a charm! Thanks dude

[Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete]

[mdmackillop]

There are many ways to select a range. If you find an issue, try highlighting the range to check it is what you are after as you step through the code

Set r = Cells(1, 1).CurrentRegion
r.Interior.ColorIndex = 6