Delete row messing with formulas

[Trueblue862]

Hi, I have this piece of code which I'm using to delete products from a database, but when I run it I get a #REF error on other sheets which have a formula dependent on the items I have deleted. I have tried replacing "Delete" with "ClearContents" and then running my sort macro. This doesn't give me the problem with the formulas, but the sort macro gets stuck on the empty row and doesn't move past it. What would be the best way to solve this problem.

This is my delete macro.

Private Sub DeleteRow()
    Dim iRw As Integer
    Dim wsWS As Worksheet
    Set wsWS = Worksheets("Order Sheet")
    iRw = cbxItem.ListIndex + 2 'listindex starts at 0, plus a header row, so +2
    wsWS.Cells(iRw, 1).EntireRow.Delete
End Sub

This is my sort macro.

Sub Sort_Me() 
    With Worksheets("Order Sheet").Range("$A$2").CurrentRegion
        .Sort Key1:=.Columns(1), Header:=xlYes, Order1:=xlAscending   
    End With     
End Sub

 

[jasonb75]

I think that the problem will be in the formulas, not the code, however there is not enough information to say for certain.

 

[Trueblue862]

It quite likely is. Here is a sample of one of the sheets, if this helps.

Stock List 1.5.xlsm
	A	B
1	Product	Cost per Unit
2	Apples (pink lady)	$1.25
3	Avocado	$1.00
4	bananas (cavendish)	$2.13
5	bananas (sugar)	$2.50
6	beans	$2.25
7	beetroot	$1.60
8	Blue berrys	$3.50
9	broccoli	$1.00
10	brussel sprouts	$0.80
11	cabbage (red)	$2.20
12	Capsicum (green)	$3.75
13	capsicum (red)	$1.50
14	carrots	$1.20
15	cauliflower	$2.50
16	celery	$2.50
17	corn	$2.80
18	cuccumbers (leb)	$4.40
19	cucmber (con)	$1.73
20	eggplant	$3.50
21	garlic (russion globed)	$0.00
22	ginger	$0.00
Cost Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B200	Expression	=MOD(ROW(),2)=1	text	NO

 

[jasonb75]

Yep, that helps a great deal. Looking at your formulas, you will see that each one refers to a specific individual cell, once that cell is deleted it no longer exists, the formula can't find it so it shows the #REF! error that you see.

As far as I can see from your example, the formulas are a direct copy of columns A and J of 'Order Sheet', try this formula in A1, then drag Right and Down.

=INDEX(('Order Sheet'!$A:$A,'Order Sheet'!$J:$J),ROW(),,COLUMN())

 

[Trueblue862]

Yep, that helps a great deal. Looking at your formulas, you will see that each one refers to a specific individual cell, once that cell is deleted it no longer exists, the formula can't find it so it shows the #REF! error that you see.

As far as I can see from your example, the formulas are a direct copy of columns A and J of 'Order Sheet', try this formula in A1, then drag Right and Down.

=INDEX(('Order Sheet'!$A:$A,'Order Sheet'!$J:$J),ROW(),,COLUMN())

That formula isn't working, it's giving me a #REF! error.

 

[jasonb75]

That formula isn't working, it's giving me a #REF! error.

That might be down to me trying to be a bit too clever and do it in a single formula this should work in column A

=INDEX('Order Sheet'!$A:$A,ROW())

Column B

=INDEX('Order Sheet'!$J:$J,ROW())

If that gives you an error too then it would suggest another problem that is not visible in the example.

 

[Trueblue862]

Thank you. It works perfectly. I knew it would be a fairly simple fix, but I'm only fairly new to all this.

 

[Trueblue862]

Jasonb75, how would you then wrap up an if function into an index function?

=IF('Order Sheet'!N2>=0,'Order Sheet'!A2,"")

 

[Trueblue862]

Never mind, I figured it out. Thanks for your help.
=IF(INDEX('Order Sheet'!$N:$N,ROW())>=0,INDEX('Order Sheet'!$A:$A,ROW()),"")