vba cell find empty and change value in different cell

Question

I need help with this problem:

If any cell in column A is empty then in Columns D and E delete cells in the same row but shift up only cells in Columns D and E, Column A will stay untouched...

Macro code for this situation in principle for cells shifting is :Sub Macro2() ' ' Macro2 Macro ' ' Application.WindowState = xlNormal ActiveCell.FormulaR1C1 = "a" Range("D1").Select Selection.Delete Shift:=xlUp Range("E1").Select Selection.Delete Shift:=xlUp End Sub Convert it to suitable and proper VBA code. — skkakkar
– skkakkar, Commented Jul 15, 2016 at 17:16

score 1 · Accepted Answer · 2016-07-16 06:44:24Z

1

Sub DeleteUP_D_and_E_IF_A_IsEmpty()

    Dim LastRow As Long, x As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row


    For x = 2 To LastRow

        If Cells(x , 1) = "" Then Range(Cells(x, "D"), Cells(x, "E")).Delete Shift:=xlUp

    Next


End Sub

edited Jul 16, 2016 at 6:44

answered Jul 16, 2016 at 3:38

user6432984

Sign up to request clarification or add additional context in comments.

6 Comments

Robin Mackenzie Over a year ago

Why do you need a Chicken ? ;)

user6432984 Over a year ago

lol..Do you know what they have to eat at work today? Fried Chicken. Do you know what they had yesterday? Baked Chicken. I don't know what there are severing tomorrow. Must be Mystery Chicken!!!

lonestorm Over a year ago

You have switched the row variable from i to x. This will work if you use i as the row reference for the cells (e.g. `Cells(i, 1)).

lonestorm Over a year ago

If I understand the question, I think you need to delete column A and shift those cells too. I would presume columns B and C should be deleted (i.e. the whole range A:D) otherwise the data will be 'corrupted'.

user6432984 Over a year ago

lo that was stupid. I converted a sub from a previous answer over and left several variables. Thanks guys for giving me a chance to salvage my dignity!

|

Collectives™ on Stack Overflow

vba cell find empty and change value in different cell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related