Deleting cells

[sergemaster]

Greetings all,
I am attempting to delete the contents of every third cell in column E going in a decending order. There are several thousand lines of data so I am looking for a formula that can automate this process, can anyone help?

Cheers,
Serge

 

[schielrn]

Welcome to the Board!

You could put this formula in an empty column and then filter for true and then delete. It should put true in every 3rd cell:

=mod(row(a1),3)=0

Or you could use vba code to do this.

Hope that helps.

 

[MrKowz]

To clarify, you are starting on row 1, and you want to clear the contents of E1, E4, E7, E10, etc?

 

[jim may]

Sample Spreadsheet BEFORE Running Macro Below:

Excel Workbook
	A	B	C	D	E
1					My Stuff
2					1
3					2
4					3
5					4
6					5
7					6
8					7
9					8
10					9
11					10
12					11
13					12
14					13
15					14
16					15
17					16
18					17
19					18
20					19
21					20
22					21
23					22
24					23
25					24
26					25
27					26
28					27
29					28
Sheet1

Excel 2007

Sub Foo()
For i = 3 To 30 Step 3
Cells(i + 1, 5).ClearContents
Next i
End Sub

Spreadsheet After running Macro Foo():

Excel Workbook
	A	B	C	D	E
1					My Stuff
2					1
3					2
4
5					4
6					5
7
8					7
9					8
10
11					10
12					11
13
14					13
15					14
16
17					16
18					17
19
20					19
21					20
22
23					22
24					23
25
26					25
27					26
28
29					28
Sheet1

Excel 2007

 

[sergemaster]

Hey guys, thanks for the QUICK responses!

I am starting on cell E3 and need to delete in decending order to E28206. I hope this helps..

Cheers,
Serge

 

[jim may]

I am starting on cell E3 and need to delete in decending order to E28206. I hope this helps..
So Try...

Sub Foo()
For i = 3 To 28206 Step 3
Cells(i, 5).ClearContents
Next i
End Sub

 

[MrKowz]

Give this a go:

Public Sub DeleteThirdRow()
Dim i   As Long, _
    LR  As Long
    
LR = Range("E" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 3 To LR Step 3
    Range("E" & i).ClearContents
Next i
Application.ScreenUpdating = True
End Sub

 

[sergemaster]

Thanks everyone for responding. Please forgive my inexperience with Excel, but where can I copy and paste one of these formulas into so I can give them a whirl and see if it works.

Cheers,
Serge

 

[MrKowz]

Thanks everyone for responding. Please forgive my inexperience with Excel, but where can I copy and paste one of these formulas into so I can give them a whirl and see if it works.

Cheers,
Serge

For the code supplied by JimMay or myself, press Alt+F11 to bring up the VBA Editor. In here, go to Insert>Module. Then copy and paste either of our code into the window on the right side. Go back to your spreadsheet and press Alt+F8 to bring up the Macros window. Choose the name of the macro from the list and press "Run".

 

[sergemaster]

Hey guys,
Not only did it work like a charm, but I learned something also! Although I have another question that I will post in a seperate thread, I want to thank all of the Excel Gurus that helped me with this one!

Cheers,
Serge