1

I am new to VBA programming, and I need some help writing a simple macro in Excel 2010.

I need to search for a text string in Column A (the exact text I'm searching for is not specified) and if that string is found within the cell, cut and paste that cell's entire row into another sheet in the workbook and then delete the empty rows in the original sheet.

I searched the forum a bit and found some code examples that almost got me where I wanted to get but not quite.

Community
  • 1
  • 1
BranpanMan
  • 21
  • 1
  • 1
  • 5
  • possible duplicate of [How to copy a line in excel using a specific word and pasting to another excel sheet?](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) – Siddharth Rout Dec 10 '13 at 00:09

4 Answers4

1

OK. I used the recorder, and hints that you all left me, and came up with this macro. I'm not using an incrementor or looping it, but rather filtering it and doing it all in one go. This process has worked for me and now everyone in my office... and everyone now thinks I'm really good at VBA... which is NOT the case, but I'm certainly on my way :)

Thanks everyone, for the help!!!

Sub MoveNotSpec()

'Filters for "Not Specified" on main sheet and cuts, pastes rows into new sheet and deletes empty rows on main sheet'

Selection.AutoFilter
ActiveSheet.Range("A1:A2000").AutoFilter Field:=1, Criteria1:= _
    "=*specified*", Operator:=xlAnd
ActiveSheet.UsedRange.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("OtherSheet").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("FirstSheet").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveSheet.Range("A1:A2000").AutoFilter Field:=1

End Sub

BranpanMan
  • 21
  • 1
  • 1
  • 5
0

If you use the macro recorder, you can record the whole process you describe, then view the generated code. Because the script won't need to remember the places where it found "not specified", just loop until find can't find the string anymore. Also, remember to keep a row incrementor for the destination sheet.

bf2020
  • 742
  • 4
  • 7
  • OK. I'm getting closer: I used a lot of Cilla's above example plus the Macro Recorder to help. I'm currently able to find the strings and copy the rows into the destination sheet. But I'm having problems looping and using the row incrementor; I'm looping with: Do While ActiveCell.Value <> Empty Loop But that just ends up deleting everything on the original sheet! In the new sheet I added: Sheets("OtherSheet").Select Range("A" & intPasteRow).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste I added the Offset to try and increment the rows but it just pastes over the same rows. – BranpanMan Dec 10 '13 at 17:01
  • I forgot - if you don't care about the order of rows then you can "cut" on the source sheet and use "insert" on the destination sheet, thus obviating the need to increment a counter for the transfer process. Maybe try taking the "delete" code out of Cilia's answer? – bf2020 Dec 10 '13 at 18:40
0

Maybe try something similar to this:

Sub quickexample()

Call FilterData("not specified", xlFilterValues)
ActiveSheet.UsedRange.Copy
Worksheets.Add.Name = "NewSheet"
Worksheets("NewSheet").Paste

End Sub

Function FilterData(criteria, voperator)
[A1].AutoFilter Field:=1, _
Criteria1:=criteria, _
Operator:=voperator

End Function

Venzan
  • 1
  • 1
0

Possibly try something like what I posted below. I got the bulk of it just from recording a macro, like another user suggested. If you're new to VBA just recording a macro is the best place to start most of the time! 

Dim intPasteRow As Integer
intPasteRow = 2

Sheets("FirstSheet").Select
Columns("A:A").Select
On Error Resume Next
Selection.Find(What:="not specified", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
If Err.Number = 91 Then
    MsgBox "ERROR: 'not specified' could not be found."
    End
End If

Dim intRow As Integer
intRow = ActiveCell.Row
Rows(intRow & ":" & intRow).Select
Selection.Cut

Sheets("OtherSheet").Select
Range("A" & intPasteRow).Select
ActiveSheet.Paste

Sheets("FirstSheet").Select
Rows(intRow & ":" & intRow).Select
Selection.Delete Shift:=xlUp
cilla
  • 46
  • 4
  • OK. I'm getting closer: I used a lot of your example plus the Macro Recorder to help. I'm currently able to find the strings and copy the rows into the destination sheet. But I'm having problems looping and using the row incrementor; I'm looping with: Do While ActiveCell.Value <> Empty Loop But that just ends up deleting everything on the original sheet! In the new sheet I added: Sheets("OtherSheet").Select Range("A" & intPasteRow).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste I added the Offset to try and increment the rows but it just pastes over the same rows. – BranpanMan Dec 10 '13 at 17:05
  • One thing you should remember is that with a DO WHILE you have to specifically say when you move to the next row (like i = i + 1). And you might be having the same problem with the "OtherSheet" rows getting overwritten. Just make sure you are using variables correctly to keep moving through the cells and sheets. – cilla Dec 11 '13 at 00:54