PDA

View Full Version : Q: Create Macro to Archive and then Clear Specific cell. Clearing Issue.



Lojack84
10-01-2015, 08:28 PM
I have put together a Macro that allows me to archive Data from one sheet to another however I am having trouble having it Clear the info afterwards. The first Column contains numbers that I do not want to clear, right now it is only clearing the data in column B.
If someone could take a look at this I would be very greatful.
'Sub archive()
Dim i, lastrow
Dim mytext As String
lastrow = Sheets("Rooms").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lastrow
mytext = Sheets("Rooms").Cells(i, "F").Text
If InStr(mytext, "yes") Then
Sheets("Rooms").Cells(i, "A").EntireRow.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Rooms").Cells(i, "B").Clear
End If
Next i
End Sub'

p45cal
10-02-2015, 05:06 AM
try:
Sub archive()
Dim i, lastrow
With Sheets("Rooms")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To lastrow
If InStr(.Cells(i, "F").Text, "yes") Then
With .Cells(i, "A").EntireRow
.Copy Destination:=Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Resize(, .Columns.Count - 1).Offset(, 1).Clear
End With
End If
Next i
End With
End Sub

p45cal
10-02-2015, 05:14 AM
Grrr. You've cross posted this without including links to where you have done so:
http://stackoverflow.com/questions/32899296/q-how-to-clear-cells-after-archiving
http://www.mrexcel.com/forum/excel-questions/891105-visual-basic-applications-code-issue-problem-clearing-selected-cells-after-archive.html

So the time I spent putting the above together was totally wasted and unnecessary; you already had a solution 7 hours ago.
Lojack84, have a read of http://www.excelguru.ca/content.php?184 and please, when you do cross post, don't do so without links.