Solved

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

Posted on 2013-01-17
11
1,043 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

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.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

756 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