PDA

View Full Version : Solved: VBA to delete all "unused" formulas



JimS
01-21-2009, 01:27 PM
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
01-21-2009, 01:45 PM
Dim c As Range
For Each c In Sheets("Data").Range("A31:A300")
If c.Formula <> "" And c.Value = "" Then c.Delete (xlShiftUp)
Next

Kenneth Hobs
01-21-2009, 02:10 PM
When deleting rows, work from the bottom up.
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

JimS
01-21-2009, 02:48 PM
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
01-21-2009, 03:33 PM
I guess you could delete the 3 from 31 to make it go to row 1. This will get the last row.
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

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
01-21-2009, 05:35 PM
Thank you very much...