Solved: VBA to delete all "unused" formulas

[JimS]

Does anyone have any code that can delete all formulas that are not using used.

I have a sheet called "Input" that gets populated via an Import of a .csv file.

On a sheet called "Data" I have references to the "Input" sheet via the following formula:

=IF('Input'!$A$1="","",'Input'!$A$1)

This formula is replicated down to row 300 (Cell A300) on the Data sheet.

On the "Input" sheet there is only 30 entries - this time, it can vary up to 300 entries.

How can I delete the "unsed" formulas that are in A31:A300 on the "Data" sheet?

This might not be the best way but eventually I'm trying to have the Charts that reference the "Data" sheet automaticlly adjust the Source Rows to match the number of entries on the Input (or Data) sheet.

Thanks for any and all help...(God knows I need it)

Jim

[nst1107]

[vba]Dim c As Range
For Each c In Sheets("Data").Range("A31:A300")
If c.Formula <> "" And c.Value = "" Then c.Delete (xlShiftUp)
Next[/vba]

[Kenneth Hobs]

When deleting rows, work from the bottom up.
[VBA]Sub DelRows()
Dim c As Range, i As Long
For i = 300 To 31 Step -1
Set c = Cells(i, "A")
If c.HasFormula And c.Value = "" Then c.EntireRow.Delete (xlShiftUp)
Next i
End Sub[/VBA]

[JimS]

Can the range be:

.Range("A1:A300") so that if there are less then 30 entries it will delete all the "unused" formulas?

Any idea how to get a Chart to automatically adjust based on the number of rows in the source table?

[Kenneth Hobs]

I guess you could delete the 3 from 31 to make it go to row 1. This will get the last row.
[VBA]Sub DelRows()
Dim c As Range, i As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
Set c = Cells(i, "A")
If c.HasFormula And c.Value = "" Then c.EntireRow.Delete (xlShiftUp)
Next i
End Sub[/VBA]

Your last question is off topic. It is best to start a new thread for such. You can use Dynamic Named ranges or use the Change event to reset the SeriesCollection. I prefer the first. See http://peltiertech.com/Excel/Charts/Dynamics.html

[JimS]

Thank you very much...