VBA Excel error - "You cannot change part of an array"


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

Open in new window

Is there a work around?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

SteveConnect With a Mentor Commented:
OK, have removed the Transpose and added an INDEX MATCH

Then put in Fixed values from 2 to 271.. this will alow you to delete rows.

Have a look and see if it does the job.

Could also use the orininal file and copy paste the values before deleting etc.

you could use :
Right after screenupdating off to set the values and remove the transpose.
This has been included in the other question answer too.
Sheets("Sheet2").Range("B2:H283").value = Sheets("Sheet2").Range("B2:H283").value

Open in new window

Michael FowlerSolutions ConsultantCommented:
I believe the problem above was that you had set rng1 as a range of values but then tried to delete one of these values. This code should work for you.

   Dim currRow As Long
    currRow = Range("G" & Rows.Count).End(xlUp).Row
    While currRow >= 12
        If Range("G" & currRow).Value = "0" Then Range("G" & currRow).EntireRow.Delete
        currRow = currRow - 1

Open in new window

mhdiAuthor Commented:
Unfortunately this still has the same issue.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Michael FowlerSolutions ConsultantCommented:
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
mhdiAuthor Commented:
Ah yes, you maybe right.

I have actually started another question because I thought it was an unrelated issue... http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27999996.html
Is line 2 correct? There is a minus where there is normally an equals sign.
mhdiAuthor Commented:
@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.
mhdiAuthor Commented:
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
mhdiAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.