Check, Cut and Paste, Delete Row, move

Question

I'm trying to do the following:

check if the cell from column A is blank,
if yes, cut vale from column C (same row)
Paste cut value to column D (1 row above)
Delete blank row (row3)
Loop until row 1302

scenario:

Check row A1, empty = false.
Check row A2, empty = false.
Check row A3, empty = true.
Cut C3. Paste to D2.
Check row A4, empty = false. Check row A5, empty = true.
Cut C5. Paste to D4.
Delete row 3 (for being blank)
Loop until row 1302.

So far

Sub Macro5()
      Range("A2").Select
      Do Until IsEmpty(ActiveCell)
         ActiveCell.Offset(1, 0).Select
      Loop

End Sub

TIA

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • What exactly is your question? You didn't ask one. Where did you get stuck or errors? What did you stop from continuing? Did you try the Macro Recorder? — Pᴇʜ, Sep 08 '20 at 09:01
By cut do you mean to delete that cell in column c and shift the data up? If so your data will come out of line the first blank instance. Or do you want to delete the whole row after copying column C to D? This would probably need to be looped in reverse if you do want to delete. Otherwise if you have a blank in A3, then when you delete that row, A4 will become A3 but your next loop will go to A4 meaning you skip what was A4 because it's now A3. — Simon, Sep 08 '20 at 09:08
I guess to avoid skipping, I can focus on just cutting C3 and pasting it to D2.Then run another macro to clean-up the blank rows. — Gab G, Sep 08 '20 at 10:24
There's no need for two macros. The one just needs to run from the bottom up. Again are you deleting (where a row in "A" has a blank) just Column C or the entire row or A:C ? — Simon, Sep 08 '20 at 10:58
After copying C3 to D2, I'm deleting the blank rows. Row 3 will be blank after cutting C3. The whole Row 3 will be deleted and Row 4 will move up to become Row 3. — Gab G, Sep 08 '20 at 11:05

score 0 · Answer 1 · answered Sep 08 '20 at 11:06

Since you are going to delete rows, loop from bottom to top

For rw = 1302 to 1 step -1

Test a Cell using the Values property

    If IsEmpty(Cells(re, 1).Value) Then

Assuming you don't need to copy Formats, just copy the value

        Cells(rw - 1, 4).Value = Cells(rw, 3).Value

Delete the row

        Rows(rw).Delete

Move on

   End If
Next

score 0 · Answer 2 · answered Sep 08 '20 at 11:13

Here you go. Change the couple things that may need to be changed. I've set it to run between row 2 to row 1302. It loops from the bottom up which is needed when deleting rows.

Sub CutC()

Dim i As Long, ws As Worksheet

Set ws = Sheets("Sheet1") 'set your sheet name

For i = 1302 To 2 Step -1 'This is your range to work from. Going from bottom row (1302) to top row (2)
    If ws.Range("A" & i) = "" Then
        ws.Range("D" & i - 1) = ws.Range("C" & i)
        ws.Range("A" & i).EntireRow.Delete
    End If
Next i

End Sub

xwhitelight · Answer 3 · 2020-09-08T10:29:35.990

-1

Enter this formula to D2 and fill down: =if(A3='', C3)

Copy column D and paste as value.

Filter blank cells in column A, clear content or delete entire rows is up to you.

edited Sep 08 '20 at 10:29

answered Sep 08 '20 at 09:25

xwhitelight

1,569
1
10
19

@GabG did you try it? – xwhitelight Sep 08 '20 at 10:27
Oh, I didn't notice there's a header. Updated the formula – xwhitelight Sep 08 '20 at 10:29
I did, but once I deleted the line 3 then D2 will be zero. I want to cut C3 to D2. Your method is using C3 as a reference for the value of D2. Let me update the question. – Gab G Sep 08 '20 at 10:42
@GabG That's why I said copy column D and paste as value, which means clear the formula before you clear content of references – xwhitelight Sep 08 '20 at 10:45

Check, Cut and Paste, Delete Row, move

3 Answers3