PDA

View Full Version : Solved: Delete Row but store a value first!!



f2e4
07-04-2008, 01:54 AM
Hey guys,

I'm using the following code to look down column C for a value (staffdropdown) and if found, delete the entire row.

However, before the code deletes the row. is there a way to store the value that was in column D of that row - it is to be used later

This is waht i'm using so far - the delete section works fine but the store value thing is bugging the life out of me:

Dim NextRow As Long
'FIND LAST ACTIVE ROW
With ActiveSheet
NextRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

Dim FirstRow As Long
Dim staffname As String
Dim LastRow As Long
Dim Lrow As Long
With ActiveSheet
.Select
FirstRow = 4
LastRow = NextRow
For Lrow = LastRow To FirstRow Step -1
With .Cells(Lrow, "C")
If Not IsError(.Value) Then
If .Value = staffdropdown Then
'STORE VALUE IN COLUMN D
Cells(Lrow, "D").Value = staffname
'DELETE ROW
.EntireRow.Delete
End If
End If
End With
Next Lrow
End With

mdmackillop
07-04-2008, 06:04 AM
So you're storing the value in column D, then deleting the row where you have just stored your data?

f2e4
07-04-2008, 07:00 AM
Maybe thats where i'm going wrong...

I wanted to look down column c and find a value.

Once found, the code should store the value from column D

then delete the entire row

mdmackillop
07-04-2008, 07:06 AM
Store it where?

f2e4
07-04-2008, 08:26 AM
had a play around with some different code and don't need to modify the above anymore so you can ignore this request

thanks anyways

Aussiebear
07-04-2008, 06:52 PM
f234, do you read the posts by other members? Why would someone want to assist you if you are just going to ignore their posts? Please show a little more respect to those who make an effort to assist you, or you run the risk that these people will simply bypass your requests.

f2e4
07-06-2008, 01:26 PM
f234, do you read the posts by other members? Why would someone want to assist you if you are just going to ignore their posts? Please show a little more respect to those who make an effort to assist you, or you run the risk that these people will simply bypass your requests.

Well I apologise then....but the weird thing is that i actually replied to the post above and have only just realised it isn't there. May have closed the window a little too quickly.

What I had written in my reply was that I wanted the value stored for use in the next line of code (not shown in the code above) e.g.


fullname = staffdropdown & "," & " " & staffname

mdmackillop
07-06-2008, 01:30 PM
Unless you write the data to the sheet, it will be lost when the macro finishes and variables are cleared.

f2e4
07-06-2008, 01:37 PM
The macro finishes with deleting a row from another sheet where a value in column F = fullname.

So it wouldn't be a problem where the variables are cleared and the macro is closed.

I only need the value "staffname" to be stored and used in the "fullname" function.

mdmackillop
07-06-2008, 01:50 PM
As i see it, fullname can change with each loop. You can either use it in the code, pass it to another sub or save it in an array to be used "later".

Why so parsimonious with posting your code and explanations. This could have been solved by now if I could understand what you are trying to do.

Aussiebear
07-07-2008, 06:08 AM
Well I apologise then....but the weird thing is that i actually replied to the post above and have only just realised it isn't there. May have closed the window a little too quickly.

What I had written in my reply was that I wanted the value stored for use in the next line of code (not shown in the code above) e.g.


fullname = staffdropdown & "," & " " & staffname


f2e4, your apology is accepted, please understand that what you write in the posting box wil be printed ( other than that which breeches the forums' good language policy). If you genuinely believe that something which has been written by yourself, prior to clicking the submit reply button has not been posted correctly, then please forward this issue to the Administrators of the Forum. It would be a first, to the best of my knowlege, where this is happening.