Solved: Moving Rows with criteria

[vzachin]

Hi,

I need help moving rows of data when there is a leading asterik beginning in cell B5 from sheet1 to sheet2.
The rows of data can be 1 row to maybe 20000.



thanks
zach

[geekgirlau]

' this range contains the data and the headings
With Sheets("Sheet1").Range("DataRange")
' filter the data
.AutoFilter Field:=2, Criteria1:="=~*B*"

' assumes that this is a one-off exercise - no checking in
' place for the sheet, or existing data on the sheet
.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")

' this range contains all the data excluding the headings
Sheets("Sheet1").Range("DataOnly").SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete

' remove the filter
.AutoFilter
End With

[vzachin]

hi geekgirlau,

i modified the code slightly in order to make it work properly for me. is there another way of defining the delete range instead of me using
Range("A5:E2000")?



Sub MoveRows()
With Sheets("Sheet1").Range("A4")

Range("A4:E4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=~**"
.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Range("A5:E2000").SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete
Selection.AutoFilter
End With
End Sub


thanks again
zach

[vzachin]

hi,

When there are no rows with a leading *, i get the following error message:
RUN TIME ERROR '1004' No Cells Were Found.
The error appears here:
Rows("5:" & iLastRow + 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A5")


Is there any way around this?



Sub MoveRows()
With Sheets("Sheet1").Range("A4")

Range("A4:E4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=~**"
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("5:" & iLastRow + 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A5")

Sheets("Sheet1").Select

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("5:" & iLastRow + 1).SpecialCells(xlCellTypeVisible).Delete

Selection.AutoFilter
End With
End Sub


thanks again

[geekgirlau]

Sub MoveRows()
With Sheets("Sheet1").Range("A4")
Range("A4:E4").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="=~**"
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

If iLastRow > 4 Then
Rows("5:" & iLastRow + 1).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet2").Range("A5")

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows("5:" & iLastRow + 1).SpecialCells(xlCellTypeVisible).Delete
End If

Selection.AutoFilter
End With
End Sub


However I get dodgy results using your method of autofilter. Personally my preference would be to have a dynamic range name that adjusts to the number of rows in your data.

[vzachin]

hi geekgirlau,

how would i do this:
"dynamic range name that adjusts to the number of rows in your data"

thanks
zach

[geekgirlau]

Ah, this is still one of my all-time favourites ...

Let's say you create a dummy range of values from A1 to C5. Select Insert | Name | Define and give your range a name. Now in the Refers To box you enter a formula:

=OFFSET($A$1,0,0,COUNTA($A:$A),3)

The parameters are:

Starting point (in this case, A1)
Offset rows (if you need to start the count further down than your starting point)
Offset columns (if you need to start the count to the right of your starting point)
Number of rows (by using COUNTA, you are dynamically counting the number of rows you need. Make sure that you pick a column that is always populated for this to work.)
Number of columns (you can either set a specific value here, or use COUNTA again for the columns if there is no further data to the right.

Once you define the range, add some more data below the existing dummy data. If you go back into Insert | Name | Define and click on Refers To, you'll see that the new data has automatically been included in the range.

You'll notice that you cannot use "Go To" or the name box to move to your range if you define it this way, but this doesn't affect the way that VBA interacts with your range at all.

[vzachin]

hi geekgirlau,

that is amazing! i don't quite understand how it works but for sure it does.
the "Go To" or name box does work...

thanks again for this wonderful tip.

zach