VBA code to delete Rows that show "0" in Column A

[Jnrrpg11]

Hello Folks,

This may be a quick one but my strengths are not in VBA

[/URL][/IMG]


I have a Source sheet that is linked to 1000s of formula and the whole thing is running horrifically slow.
I need a way to eliminate unnecessary rows from the source data in a fully automated way.
Luckily the desirable rows start with a 1 in column A and the undesirable rows have a 0 in Column A.

Does anyone know of some VBA code that could look down Column A for "0"s and remove the whole row.

Note - Im aware i can filter the data in Column A for "1" but the goal is to cut down on the source data (71% Reduction).
Also i know I could filter for "0" and select and delete rows but i won't be using the end spreadsheet and it needs to be as automated as possible.

Thanks for taking the time to look at this and any help is appreciated.

Richard

 

[oldbrewer]

for j=1 to 10000
if cells(j,1) = 0 then goto 10 else goto 20
10 .......code to delete a row do it with macro record to get it first
20 next j
end sub

 

[oldbrewer]

10 cells(j,1).select:Rows("13:13").Select: Selection.Delete Shift:=xlUp: goto 5

make the first row 5 for j=1 to 1000

start the loop over to avoid problems (possible)

 

[hiker95]

Jnrrpg11,

If I understand you correctly, then here is a macro solution for you to consider.

Sample raw data:

Excel 2007
	A	B
1	Services to happen w/c 3 July 17
2	1	Window Cleaning
3	0	Floor maintenance
4	0	Light Maintenance
5	1	HVAC Maintenance
6	1	Janitorial
7
Sheet1

Cell Formulas
Range	Formula
A2	=1
A3	=0
A4	=0
A5	=1
A6	=1

And, after the macro:

Excel 2007
	A	B
1	Services to happen w/c 3 July 17
2	1	Window Cleaning
3	1	HVAC Maintenance
4	1	Janitorial
5
6
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Jnrrpg11()
' hiker95, 06/30/2017, ME1012249
Dim Addr As String
Addr = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Address
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""*0"",@)),""#N/A"",@)", "@", Addr))
Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Jnrrpg11 macro.

 

[JoeMo]

Do the cells in col A have formulas in them?

 

[hiker95]

Jnrrpg11,

If there are no formulae in column A, then here is another macro solution for you to consider.

Sample raw data:

Excel 2007
	A	B
1	Services to happen w/c 3 July 17
2	1	Window Cleaning
3	0	Floor maintenance
4	0	Light Maintenance
5	1	HVAC Maintenance
6	1	Janitorial
7
Sheet1

And, after the new macro:

Excel 2007
	A	B
1	Services to happen w/c 3 July 17
2	1	Window Cleaning
3	1	HVAC Maintenance
4	1	Janitorial
5
6
7
Sheet1

With the same instructions as my last reply #4.

Sub Jnrrpg11_V2()
' hiker95, 06/30/2017, ME1012249
Dim Addr As String
Addr = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Address
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""*0"",@)),""#N/A"",@)", "@", Addr))
Range(Addr).SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

With the same instructions as my last reply #4.

Then run the Jnrrpg11_V2 macro.