DELETING CELLS AFTER SEARCHING

Posted by PLEASE HELP!!! on September 21, 2001 12:50 AM

I've got a problem! I want this to work with using a macro! With the VB! I've got in worksheets "Sheet1" all data from column 1 til column 26! In "Sheet2" cell "G10" and "G12" I wanna be able to fill in a date and that it will search in column 3 on Sheet1 in which all the dates are! If it finds a match with the dates or dates in between the dates filled in G10 and G12 that it will delete those dates in Sheet1 and that if for example it finds a date that matches it will delete all the data in that row from column 1 til column 26! For example if I have in Sheet2 G10 as date "9-01-01" and in G12 "9-20-01" that it will delete all the rows from column 1 til column 26 which contains a date that's between the dates in Sheet2 cells G10 and G12! And when that is done that it gives a message which says "Delete is completed"! Hope someone can help me? Thanks!

Posted by Rob Jackson on September 21, 2001 1:30 AM

The following code works for between and including the values on Sheet2.

Sub Deleter()
Dim Var1 As Date
Dim Var2 As Date
Dim StartLoop As Long
Dim RowLoop As Long
Dim Check As Date

Var1 = Range("'sheet2'!G10").Value
Var2 = Range("'sheet2'!G12").Value

StartLoop = Range("C65536").End(xlUp).Row
For RowLoop = StartLoop To 1 Step -1
If IsDate(Range("'Sheet1'!C" & RowLoop).Value) Then
Check = Range("'Sheet1'!C" & RowLoop).Value
If Check >= Var1 And Check &LT;= Var2 Then
Sheets("Sheet1").Rows(RowLoop & ":" & RowLoop).Delete Shift:=xlUp
End If
End If
Next RowLoop
Display = MsgBox("Job Complete!", 48, "Advisory.")
End Sub

Rob.

Posted by HELP ME on September 21, 2001 1:57 AM

Thanks for helping me but it's not working. Can you tell me more about what it all mean what you said? Like what does the '!C' means? It just gives the message that's the only thing it does nothing else. Hope you can help me out?

Posted by Rob Jackson on September 21, 2001 2:50 AM

No problem...

This bit declares the variable types

This bit sets the upper and and lower variable limits from the cells on Sheet 2 (G10 and G12)
When declaring the range the format is
"'sheetname'!range reference"
where range reference is in A1 format. The sheet name is put in single ' so that if it has a space in it the compiler understands that its all part of the same string.

This bit finds the bottom of your table so that the scan can work from top to bottom. If it goes the other way it will drop out of sinc when a line is deleted. ie loop goes 1,2,3... if line 2 is deleted, line 3 becomes the new line two but the loop has incremented to 3 so the new line 2 is not inspected. Going bottom to top means this is not an issue.

The loop Runs from bottom to top. First it checks if the contents of the cell being checked is a date. If it is then it checks to see if it is in the required range (Between Var1 and Var2) and if it is then it removes that row. If not then it checks the next row up. The construction of the range reference is the same as above except that the row number in the A1 ref is defined by a variable so it is moved outside the " and CONCATENATED with an & sign.

This bit Displays a message box with an Exclamation symbol advising of completion.

If it is not working, check that the contents of the cells are dates and not text strings. If they are text strings you will need to convert them to dates by adjusting the line to

Check = CDATE(Range("'Sheet1'!C" & RowLoop).Value)

Hope this helps

Rob

Posted by Help Me Again? on September 21, 2001 2:58 AM

Thanks! It works! But I've got another question now! I hope you can help me out also? Before delete the rows is it possible to let a messagebox appear which says "Are you sure you wanna delete these dates?" If yes then do it and if no then exit? Hope you can help me? I don't know where to put this in the formula! Thank you!

Posted by Rob Jackson on September 24, 2001 12:09 AM

Sorry about the delay, I've been away for a couple of days.

What you need to do is this...

::sub Macrothing
::Dim statements
::Dim statements
Display = msgbox("Do you want to continue",4,"Confirm")
if Display = 6 then
:: rest of existing code stuff
end if
::end sub

Anything starting :: should already exist. That should fix you right up.