Solved

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

Posted on 2013-01-17
11
952 Views
Last Modified: 2013-01-19
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
0
Comment
Question by:mhdi
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 38791858
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
 

Author Comment

by:mhdi
ID: 38791903
Unfortunately this still has the same issue.
0
 
LVL 23

Expert Comment

by:Michael74
ID: 38791965
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
 

Author Comment

by:mhdi
ID: 38791969
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38792047
Is line 2 correct? There is a minus where there is normally an equals sign.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:mhdi
ID: 38792503
@GrahamSkan - Yes I think its right, I want to start the loop on the second last line. Hence "rng1.Rows.Count - 1".
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38792530
Sorry. I misread the line. Thanks
0
 
LVL 24

Expert Comment

by:Steve
ID: 38792585
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
 

Author Comment

by:mhdi
ID: 38792621
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
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 38792763
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
 

Author Comment

by:mhdi
ID: 38797566
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now