How to delete cells A2&B2 if B2 is empty.

**chriscmartin** · 08-29-2014, 09:42 AM

I have a very long list of data that fills A2:A10000 and some of these have corresponding data in column B.

What I want is for excel to automatically scan the cells and if B2 = empty cell I want it to delete cells A2&B2.

I want this to happen all the way through cells B2:B10000 to remove the blanks and corresponding data.

Im not sure if I can do this with a macro or VBA but any help you can give will be greatly appreciated as im new to this.

Thanks

**ragulduy** · 08-29-2014, 09:50 AM

Assuming you mean shift the cells up by delete:

sub macro_1()
union(Range("B2:B10000").SpecialCells(xlCellTypeBlanks),Range("B2:B10000").SpecialCells(xlCellTypeBlanks).Offset(0, -1)).Delete xlup
End Sub

**chriscmartin** · 08-29-2014, 10:30 AM

Thanks, Will this also delete the cell directly to the left of it column A?

**ragulduy** · 08-29-2014, 10:42 AM

Why don't you try it and see....

(the offset(0,-1) part means to move one column to the left, so it should)

**chriscmartin** · 08-29-2014, 10:48 AM

Thanks, I tried it and get the below error

Run-time error '1004':
No cells were fount

**ragulduy** · 08-29-2014, 10:50 AM

When you say empty, do you mean there is a formula in it that equates to ""? (the special cells method will find blank as in with no formula or value).

if so, I think you need to loop through the cells

sub macro_1()
dim count
for count = 10000 to 2 step -1
  if Range("B" & count) = "" then Range("A" & count & ":B" & count).delete xlup
next count
end sub

**Vikas_Gautam** · 08-29-2014, 11:56 AM

Try this..

Sub DelRow()
For each cell in Range("B2:B10000")
if cell.value="" then Cell.select : selection.resize(-1,).delete Xlup
Next
End sub

**Vikas_Gautam** · 10-07-2014, 11:59 AM

Thanks for the feedback and reputation point...

**james8992** · 10-09-2014, 10:40 PM

I have a similar issue but I have more then just columns A & B. I have a table where every other column is Date | Amount. So it goes

Date 1 | Amount 1| Date 2 | Amount 2 | Date 3 | Amount 3......................

The dates are always populated, but the amount for that month is sometimes blank. I need to locate where the amount is blank and delete the cell and shift left and also delete the associated date that doesn't have a value and also shift that to the left. So when the amount is blank you are deleting and sifting to the left both where an amount would have been and and also the date cell to the left.

Before.PNG

Should look like this

After.jpg

**Vikas_Gautam** · 10-09-2014, 11:59 PM

Hi,
Welcome to this forum
It would have been more sensible if you have provided a raw worksheet, rather than clicking a photo..

Nevertheless, here is the code you are looking for
untested though...

Sub ForJames()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For r = 2 To lr Step 1
    For c = 2 To lc Step 2
        If IsEmpty(Cells(r, c)) Then _
            Cells(r, c).Select: _
            Selection.Resize(0, -1).Delete Shift:=xlToLeft: _
            c = c - 2
    Next
Next
Application.ScreenUpdating = True
End Sub

If you require any other adjustment, just give me a Message....

**HaHoBe** · 10-10-2014, 12:19 AM

Hi, james8992,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Ciao,
Holger

How to delete cells A2&B2 if B2 is empty.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Re: How to delete cells A2&B2 if B2 is empty.

Thread Information

Users Browsing this Thread

Similar Threads

Filling Empty cell value based on if else condition and delete row more than 2 cells empty

Count Consecutive Empty Cells and Delete non-blank cells by specified number of days.

[SOLVED] still have empty cell after run Delete empty cells code

Delete Empty Cells With VBA

Delete empty cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions