ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Record deletion (https://www.excelbanter.com/excel-worksheet-functions/212797-record-deletion.html)

DDD December 7th 08 10:54 PM

Record deletion
 
I have 2 questions

1)
Is there any way to link a macro & cell together.
For example, i have a cell that has a kind of count down in days, updated
every day (open the spreadsheet today & its 10, open it the next day & its 9)
when the number within the cell reaches 0, it activates a macro that deletes
the entire row THAT the cell (that has 0 in it) is include within.

2)i have formulae from I5-I35 & K5-K35, is there any way, i cant stop these
getting deleted (From the method used above).

The result i want to get, is that i want a row to be selected & deleted
except the cells that have a formula in them. Then all the cells under this
move up 1 row (The formulae never move)


Sorry if this is hard to understand

Eduardo December 8th 08 03:05 PM

Record deletion
 
Hi DDD,
If the cell you won't to delete is in A1 you can add to your macro something
like this
Sub Macro1()
'
' Macro1 Macro
'

Dim myrange, MyRange1 As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A10:F" & lastRow)
If Range("A1") = 0 Then

myrange.Delete
End If
End Sub
Change F to the last column you want to delete

"DDD" wrote:

I have 2 questions

1)
Is there any way to link a macro & cell together.
For example, i have a cell that has a kind of count down in days, updated
every day (open the spreadsheet today & its 10, open it the next day & its 9)
when the number within the cell reaches 0, it activates a macro that deletes
the entire row THAT the cell (that has 0 in it) is include within.

2)i have formulae from I5-I35 & K5-K35, is there any way, i cant stop these
getting deleted (From the method used above).

The result i want to get, is that i want a row to be selected & deleted
except the cells that have a formula in them. Then all the cells under this
move up 1 row (The formulae never move)


Sorry if this is hard to understand



All times are GMT +1. The time now is 02:08 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com