Need help on deleting cells

[shrek]

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.

 

[VoG]

Try

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

 

[shrek]

Thats brilliant
Thanks VOG

 

[Richard Schollar]

Hi

Give this a try:

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]

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
	A	B	C	D
1
2	LTPBY301
3		11.19		2N04
4		06:59		2N09
5
6			DOO	5W52
7			DOO	5W52
8				1W52
9
10				2K51
11				2K88
12
13
14
15	-----------------------------------------------------------------------------------------
16	LTPBY302
17		4.15	DOO	5K08
18		12.29
19				5K08
20		SX		2K08
21		09/07/2007		2N13
22		07/09/2007
23				1N64
24				1N89
25			DP
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]

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]

Many Thanks for that VOG.
Any ideas why its not going all the way down

mon test.xls
	A	B	C	D	E	F
1	LTPBY301		2N04	4.36	Bletchley	Euston
2	LTPBY301		2N09	5.55	Euston	Nhampton
3	LTPBY301				Nhampton	Nhampton
4		DOO	5W52	08+06	Nhampton	NhampNJ
5		DOO	5W52	08+15	NhampNJ	Nhampton
6			1W52	8.24	Nhampton	Euston
7					Euston	Euston
8			2K51	9.54	Euston	MKeynes
9			2K88	11.15	MKeynes	Bletchley
10					Bletchley
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]

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]

This revised macro should do what you want provided that the cells that appear blank in column A are truly blank:

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]

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