Need help on deleting cells

shrek

Board Regular
Joined
Dec 16, 2005
Messages
244
I have data in a workbook which has blank cells in columns D and E. What im trying to find out is the a macro that will delete the row if the cells are blank in both columns D and E right the way down the workbook.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub DelblankDE()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = LastRow To 1 Step -1
    If IsEmpty(Cells(i, 4).Value) And IsEmpty(Cells(i, 5).Value) Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Give this a try:

Code:
Sub test2()
With Columns("D:E")
    .AutoFilter field:=1, Criteria1:="="
    .AutoFilter field:=2, Criteria1:="="
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub
 

shrek

Board Regular
Joined
Dec 16, 2005
Messages
244

ADVERTISEMENT

Thanks for that guys just one other thing I need to know is where the data is in cells in row A is there a macro or something that I can use to copy the data until it changes and then carry on until the data changes again. Its just that ive got over 5000 entries and dont want to copy and paste on each one.

See diag
mon test.xls
ABCD
1
2LTPBY301
311.192N04
406:592N09
5
6DOO5W52
7DOO5W52
81W52
9
102K51
112K88
12
13
14
15-----------------------------------------------------------------------------------------
16LTPBY302
174.15DOO5K08
1812.29
195K08
20SX2K08
2109/07/20072N13
2207/09/2007
231N64
241N89
25DP
Sheet1


What I mean is where the entry is in A2 I need it to copy to A15 and then from A16 to where ever the next number starts. I need this to continually go right down the worksheet.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You don't need a macro to do this:

Select Column A

Press F5

Click the Special button

Tick blanks and click OK

Press =

Press the Up arrow

Hold down CTRL and press Enter
 

shrek

Board Regular
Joined
Dec 16, 2005
Messages
244

ADVERTISEMENT

Many Thanks for that VOG.
Any ideas why its not going all the way down
mon test.xls
ABCDEF
1LTPBY3012N044.36BletchleyEuston
2LTPBY3012N095.55EustonNhampton
3LTPBY301NhamptonNhampton
4DOO5W5208+06NhamptonNhampNJ
5DOO5W5208+15NhampNJNhampton
61W528.24NhamptonEuston
7EustonEuston
82K519.54EustonMKeynes
92K8811.15MKeynesBletchley
10Bletchley
Sheet1


Where I said about the blank cells in C and D I need the thing you said done before you delete the lines.
Sorry if I forgot to mention that. Hope you can help.

Thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
A couple of thoughts

- did you select the entire column A before following the procedure?

- is for example A4 actually blank? Is it highlighted when you go through the procedure to select all the blanks?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This revised macro should do what you want provided that the cells that appear blank in column A are truly blank:

Code:
Sub DelblankDE()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlManual
Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
For i = LastRow To 1 Step -1
    If IsEmpty(Cells(i, 4).Value) And IsEmpty(Cells(i, 5).Value) Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub
 

shrek

Board Regular
Joined
Dec 16, 2005
Messages
244
Found out why your 1st sugestion didnt work VOG. Although the cells are blank the format think their not.
I hightligthed the blank cells and pushed the delete button and then run it again and it worked.

Is there anyway you can re delete the blank cells.

Thnx
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top