Need to Loop through a named range and delete all empty columns/rows

[BrianExcel]

I have a named range, "FinalEditsRange".

I need to loop through the range and (1) delete all empty columns. Then, I need to loop through the last column in the range (the header is "Percent Edit" and delete any row that is empty or contains 0.

Is it possible to do both in one block of code?

 

[alansidman]

Use XL2BB and provide us with a small sample of your data and then using the same data manually mock up what you want your solution to look like and post using XL2BB

 

[BrianExcel]

Here is the data being generated by this report. But when I pull data for different weeks, the data set can change, and so can where the blanks are (if any exist).

This data is what I am trying to test against right now.

Venus Ball Stats (5.25-6.01).xlsx
	A	B	C	D	E	F	G
1	Venue		Reads	Edited			Percent Edit
2	City1		338,102	1,026			0.303%
3	City2		6				0.00%
4	City3
5	City4		176	1			0.568%
6	City5		46,483	160			0.344%
7	City6		36,566	198			0.541%
8	City7		441,342	3,257			0.738%
9	City8
10	City9		467,605	4,435			0.948%
11	City10
12	City11		140				0.00%
13	City12		22,487	97			0.431%
14	City13		384,968	1,162			0.302%
15	City14		426,991	1,164			0.273%
16	City15		25,090	87			0.347%
17	City16
18	City17		384,357	3,486			0.907%
19	City18		422,942	684			0.162%
20	City19		32,735	97			0.296%
21	City20		438,701	2,579			0.588%
7DayEdits

 

[BrianExcel]

And here is what it looks like when done. All blanks columns removed, and any cell within with "0" or blank, the entire row is removed.

Venus Ball Stats (5.25-6.01).xlsx
	A	B	C	D
1	Venue	Reads	Edited	Percent Edit
2	City1	338,102	1,026	0.303%
3	City4	176	1	0.568%
4	City5	46,483	160	0.344%
5	City6	36,566	198	0.541%
6	City7	441,342	3,257	0.738%
7	City9	467,605	4,435	0.948%
8	City12	22,487	97	0.431%
9	City13	384,968	1,162	0.302%
10	City14	426,991	1,164	0.273%
11	City15	25,090	87	0.347%
12	City17	384,357	3,486	0.907%
13	City18	422,942	684	0.162%
14	City19	32,735	97	0.296%
15	City20	438,701	2,579	0.588%
7DayEdits

 

[alansidman]

Perfect for Power Query to solve. Here is Mcode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1", "Column2", "Column3"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Percent Edit] <> null and [Percent Edit] <> 0)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Venue", type text}, {"Reads", Int64.Type}, {"Edited", Int64.Type}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type",{{"Percent Edit", each Number.Round(_, 5), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Rounded Off",{{"Percent Edit", Percentage.Type}})
in
    #"Changed Type1"

Book3
	A	B	C	D
1	Venue	Reads	Edited	Percent Edit
2	City1	338102	1026	0.303%
3	City4	176	1	0.568%
4	City5	46483	160	0.344%
5	City6	36566	198	0.541%
6	City7	441342	3257	0.738%
7	City9	467605	4435	0.948%
8	City12	22487	97	0.431%
9	City13	384968	1162	0.302%
10	City14	426991	1164	0.273%
11	City15	25090	87	0.347%
12	City17	384357	3486	0.907%
13	City18	422942	684	0.162%
14	City19	32735	97	0.296%
15	City20	438701	2579	0.588%
Sheet2