mhdi
asked on
VBA Excel error - "You cannot change part of an array"
Hi,
This VBA code is suppose to delete the entire row of the value in column G is 0.
However I get the error "You cannot change part of an array" on line 3.
Is there a work around?
Thanks
This VBA code is suppose to delete the entire row of the value in column G is 0.
However I get the error "You cannot change part of an array" on line 3.
Set rng1 = Intersect(Sheets("SheetName").UsedRange, Sheets("SheetName").Columns("G"))
For lngRow = rng1.Rows.Count - 1 To 12 Step -1
If rng1.Cells(lngRow).Value = "0" Then rng1.Cells(lngRow).EntireRow.Delete
Next lngRow
Is there a work around?
Thanks
ASKER
Unfortunately this still has the same issue.
I initially thought your code looked fine and I tested my code and so I would guess you have some formulas in the rows being deleted. Possibly array formulas. What happens if you try to delete rows manually
Michael
Michael
ASKER
Ah yes, you maybe right.
I have actually started another question because I thought it was an unrelated issue... https://www.experts-exchange.com/questions/27999996/VBA-Excel-Error-checking-cell-value-on-cells-containing-TRANSPOSE-forumla.html
I have actually started another question because I thought it was an unrelated issue... https://www.experts-exchange.com/questions/27999996/VBA-Excel-Error-checking-cell-value-on-cells-containing-TRANSPOSE-forumla.html
Is line 2 correct? There is a minus where there is normally an equals sign.
ASKER
@GrahamSkan - Yes I think its right, I want to start the loop on the second last line. Hence "rng1.Rows.Count - 1".
Sorry. I misread the line. Thanks
If you have a sample workbook, this may help provide a solution.
It would seem that you may need to change the array formula in the workbook first.
This should be easy enough, but may cause other issues with formula etc.
It would seem that you may need to change the array formula in the workbook first.
This should be easy enough, but may cause other issues with formula etc.
ASKER
I have attached a simplified spreadsheet.
The button on Sheet2 will run macro KillRows. The values on Sheet2 come from Sheet1 using a Transpose formula.
KillRows should delete the rows on Sheet2 where column G = "".
47Kb Test Spreadsheet
The button on Sheet2 will run macro KillRows. The values on Sheet2 come from Sheet1 using a Transpose formula.
KillRows should delete the rows on Sheet2 where column G = "".
47Kb Test Spreadsheet
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've used your modified spreadsheet to fix the issue. It needed some tweaking to fit but you got me there in the end. Thanks alot. I really appreciate it.
Open in new window