Copy and Paste - but automatic

[elgoober]

Hi

Have a file that drops all the values like this

Ref
Mr1
102.0
Mr2
104
there are 2000 rows of this (+ other bits which require same treatment)

I need the value to be moved from underneath the Ref - to the next column - - can't drop the data out csv or any other way - so data can only be manipulated @ excel level.

Assume there must be some code to automate this - and if anyone can point the way, I'd be more than obliged.

Cheers
Paul

 

[HaHoBe]

Hi, Paul,

as I may have misunderstood you please check out the following code on a copy of the original file.

Option Explicit

Sub elgoober()
' assumes the list to be in Column A of the sheet
' furthermore list starts in Row 1 with a heading,
' first pair of information to be found in Row 2 (stay in row)
' and Row 3 (to be placed in Row 2 Column B).
' No formatting will be done, cell formatting is Standard by default
' Code doesn´t care if there is a value or a formula in Column B
' !!!PLESAE CHECK CODE ON A COPY OF THE ORIGINAL FILE!!!
Dim lngLastRow As Long
Dim lngCounter As Long

Application.ScreenUpdating = False
' Get last used row in Column A on active sheet
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Start a loop writing every second row startung form the bottom
' and deleting the row afterwards
For lngCounter = lngLastRow To 3 Step -2
  Cells(lngCounter - 1, 2).Value = Cells(lngCounter, 1).Value
  Rows(lngCounter).Delete
Next

Application.ScreenUpdating = True

End Sub

Ciao,
Holger

 

[elgoober]

Hi Holger

Top man

Thanks

Paul