Basic Excel Question

[MASON]

Hello

I hope someone can help with what I think is a basic excel
question .
The excel version I am working with is 8.0 . I need to match two
columns .

COLUMN A COLUMN C
ROW 1 MR-1001
ROW 2 500.00
ROW 3 MS-1002
ROW 4
ROW 5 725.25
ROW 6 MT-2004
ROW 7
ROW 8
ROW 9
ROW 10 925.00


What I need is the 500.00 in column c to be on the same row as
mr-1001 which is row 1 . 725.25 needs to be in row 3 to match up with
ms-1002 an finally
925.00 needs to be on row 6 to match up with mt-2004 . Nothing needs
moved out of the columns they are originally in . This report was
exported into excel from an accounting software program and the report
is approx. 600 pages long .

So hopefully someone can help me with this problem .

Sincerely Mason

 

[Rob van Gelder]

Sub testit()
Dim i As Long, lngRows As Long, rngCell As Range

With Worksheets(1)
lngRows = .Cells(.Rows.Count, 3).End(xlUp).Row
If .Cells(1, 1) = "" Then i = .Cells(1, 1).End(xlDown).Row Else i =
1
Do Until i > lngRows
If .Cells(i, 3) = "" Then
Set rngCell = .Cells(i, 3).End(xlDown)
.Cells(i, 3).Value = rngCell.Value
rngCell.Value = ""
End If
i = .Cells(i, 1).End(xlDown).Row
Loop
End With
End Sub

 

[David]

Sort of simple, but seems to work. It assumes you have no
more thatn 20000 rows of data, which youmay need to
change.

Have a Merry Christmas

Sub Macro1()
Do Until ActiveCell.Row = 20000
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.Offset(0, 1).Select
returncell = ActiveCell.Address
Selection.End(xlDown).Select
Selection.Cut
Range(returncell).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
End If
Loop
Range("A1").Select
End Sub

 

[Greg Wilson]

Try one of the following two options.

'Preserve spacing in Column A option
Sub Test()
Dim Rng1 As Range, Rng2 As Range
Dim C As Range, TempAr() As String
Dim i As Integer

Set Rng1 = Intersect(Columns("A"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))
Set Rng2 = Intersect(Columns("C"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))

For Each C In Rng2
i = i + 1
ReDim Preserve TempAr(i)
TempAr(i) = C.Value
Next
i = 0
For Each C In Rng1
i = i + 1
C.Offset(, 3) = C
C.Offset(, 5) = TempAr(i)
Next

Columns("A:C").Delete
End Sub

'Remove blanks in Columns A and C option
Sub Test2()
Dim Rng1 As Range, Rng2 As Range, C As Range
Dim i As Integer

Set Rng1 = Intersect(Columns("A"),
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))
Set Rng2 = Intersect(Columns("C"),
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))

For Each C In Rng1
i = i + 1
Cells(i, 4) = C
Next

Regards,
Greg

 

[Greg Wilson]

I did a sloppy copy and paste. Macro Test2 should be:

Sub Test2()
Dim Rng1 As Range, Rng2 As Range, C As Range
Dim i As Integer

Set Rng1 = Intersect(Columns("A"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))
Set Rng2 = Intersect(Columns("C"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeConstants))

For Each C In Rng1
i = i + 1
Cells(i, 4) = C
Next

i = 0
For Each C In Rng2
i = i + 1
Cells(i, 6) = C
Next

Columns("A:C").Delete
End Sub

Regards,
Greg

 

[Greg Wilson]

The lines where I specify the ranges for Rng1 and Rng2 are
better written as follows. I forgot that you can specify
a subset of the active sheet using SpecialCells. Correct
for word wrap.

Set Rng1 = ActiveSheet.Columns("A").SpecialCells
(xlConstants)
Set Rng2 = ActiveSheet.Columns("C").SpecialCells
(xlConstants)


Regards,
Greg

 

[MASON]

The lines where I specify the ranges for Rng1 and Rng2 are
better written as follows. I forgot that you can specify
a subset of the active sheet using SpecialCells. Correct
for word wrap.

Set Rng1 = ActiveSheet.Columns("A").SpecialCells
(xlConstants)
Set Rng2 = ActiveSheet.Columns("C").SpecialCells
(xlConstants)


Regards,
Greg

I want to say thank you to all that posted solutions . You have been very helpful .

Sincerely Mason