Solved

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

Posted on 2013-01-17
11
1,116 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:Michael Fowler
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:Michael Fowler
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

622 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