data manipulation question

  • Thread starter Thread starter Blaster Master
  • Start date Start date
B

Blaster Master

I've got an almost 1800 line spreadsheet in Excel. I've got to manipulate
data in it for a client. I'm needing to do the following in Excel

1. begin searching at the top of the file
2. check the check / manipulate the contents of 3 adjacent columns, ie:

____________
| A | B | C |
----------------
| | X | Y |
----------------
| | X | |
----------------
| | | |
----------------
| | X | |
----------------
| | X | Y |
----------------

3. column A is always blank
4. if column C has data in it, then
4a. move column B data to column A
4b. move column C data to column B

____________
| A | B | C |
----------------
| X | Y | |
----------------
| | X | |
----------------
| | | |
----------------
| | X | |
----------------
| X | Y | |
----------------

5. continue searching from the current location until the end of the file

Any takers on this one? Otherwise, I'm gonna have to hand move 2 cells of
data almost 1700 times.

Thanks,


--

Brad S. Russell
Network / Systems Engineer
Datamax Micro
501-603-3077 (office)
 
Sub brad()
n = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To n
If IsDate(Cells(i, 3).Value) Then
Cells(i, 1).Value = Cells(i, 2).Value
Cells(i, 2).Value = Cells(i, 3).Value
Cells(i,3).Clear
End If
Next
End Sub
 
Didn't Think there was a mention of dates

Sub brad2()
n = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To n
If Cells(i, 3).Value <> "" Then
Cells(i, 1).Value = Cells(i, 2).Value
Cells(i, 2).Value = Cells(i, 3).Value
Cells(i,3).Value = ""
End If
Next
End Sub
 
Well, I've tried it and it didn't work. This is the macro that I'm
running...

Sub brad2()
n = Cells(Rows.Count, "I").End(xlUp).Row
For i = 1 To n
If Cells(i, 3).Value <> "" Then
Cells(i, 1).Value = Cells(i, 2).Value
Cells(i, 2).Value = Cells(i, 3).Value
Cells(i, 3).Value = ""
End If
Next
End Sub


One note, I was wrong on the columns...they are G H and I

So the posed problem is below.
____________
| G | H | I |
----------------
| | X | Y |
----------------
| | X | |
----------------
| | | |
----------------
| | X | |
----------------
| | X | Y |
----------------

3. column G is always blank
4. if column I has data in it, then
4a. move column H data to column G
4b. move column I data to column H

____________
| G | H | I |
----------------
| X | Y | |
----------------
| | X | |
---------------
| | | |
---------------
| | X | |
---------------
| X | Y | |
---------------

--


Blaster Master
a.k.a.
Brad S. Russell
 
well, i'll give it a shot (but you may wish i hadn't).....

Sub brad2()
Dim ws As Worksheet

Set ws = ActiveSheet

ws.Range("g1").Select

Do Until ActiveCell = "" And _
ActiveCell.Offset(0, 1) = "" And _
ActiveCell.Offset(0, 2) = ""
If ActiveCell = "" Then
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub


well, it's sloppy & may be a little slow & isn't coded perfectly, but
it works for me........
:)
susan
 
REVISION
was checking wrong column........

Sub brad2()
Dim ws As Worksheet

Set ws = ActiveSheet

ws.Range("i1").Select

Do Until ActiveCell = "" And _
ActiveCell.Offset(0, -1) = "" And _
ActiveCell.Offset(0, -2) = ""
If ActiveCell <> "" Then
ActiveCell.Offset(0, -2).Delete Shift:=xlToLeft
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

susan
 
I want to thank you for trying, but it moved everything in the column.

I'm needing it to move the contents of column H to column G, and column I to
column H

if and only if there is something in column I

otherwise it should skip that line

--


Blaster Master
a.k.a.
Brad S. Russell
 
ahhhhh... i wasn't considering that there was other data in subsequent
columns! i was using delete to accomplish the task, but i can see
where that doesn't work, then.

back to square one..... although the concept should work (it did for
me - skipped that row when i was blank), you just need to change
ActiveCell.Offset(0, -2).Delete Shift:=xlToLeft
to whatever you can figure out about the moving the contents.

sorry!
susan
 
brad -
was thinking about this in my sleep (!) & also thought that perhaps
the skipping rows didn't work for you because your "empty" cells are
not truly empty - do they have formulas in them?
just another thought.
susan
 
Back
Top