PDA

View Full Version : [SOLVED] Excel VBA cut, paste, delete process.



andy_j
05-29-2017, 07:24 AM
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 :banghead:.

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
05-29-2017, 07:35 AM
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
05-29-2017, 07:54 AM
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
05-29-2017, 08:08 AM
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
05-29-2017, 08:19 AM
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