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.

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?

Thanks
mhdiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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

EE-test-sheet.xlsm
0
 
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
    Wend

Open in new window

0
 
mhdiAuthor Commented:
Unfortunately this still has the same issue.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
Michael
0
 
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
0
 
GrahamSkanRetiredCommented:
Is line 2 correct? There is a minus where there is normally an equals sign.
0
 
mhdiAuthor Commented:
@GrahamSkan - Yes I think its right, I want to start the loop on the second last line. Hence "rng1.Rows.Count - 1".
0
 
GrahamSkanRetiredCommented:
Sorry. I misread the line. Thanks
0
 
SteveCommented:
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.
0
 
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
0
 
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.
0
All Courses

From novice to tech pro — start learning today.