copying deleting in excel vba code

[Guest]

I have a large amount of data numbering several thousand rows.
Unfortunately for example the data in row 2 needs to be concatenated to row
1, row 4 to row 3 and sow on. Once the data has been added to row 1 for
example from row 2, to tidy the data up I would want to delete row 2, the
same would apply to what was row 4, row 6 and so on. I have tried various
VBA codes but either end up deleting all of the data or end up in a
continuous loop that I can't break out of.

Any help would be appreciated.

thanks

 

[Guest]

Hi
Eg if you have one column, A (similar with multi columns):
- in B2: =IF(iseven(ROW()),A2&A3,"").
- copy / paste the formula down.
- copy column B (menu Edit > Copy) .
- pasteSpecial as Value (menu Edit > PasteSpecial, as Value) over B
itself.
- now select the autofilter : menu Data > Filter > AutoFilter
- for column B, in the autofilter , choose empty string value
--> only row with "" will show
- select these rows and delete

I hope this helps
Sebastien

 

[Myrna Larson]

To help, please give an example of the data. You mention "concatenated", but
then use the word "added". Is this text or numeric data? How many columns are
there?

Assuming text:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
NumColumns = 5

For R = 1 To LastRow Step 2
For C = 1 To NumColumns
Cells(R, C).Value = Cells(R, C).Value & Cells(R + 1, C).Value
Next C
Rows(R).Clear
Next R

Cells(1, 1).Resize(LastRow, NumColumns).Sort _
Key1:=Cells(1, 1), Order1:=xlAscending