copy values from one column to another with criteria
 

Hello all,
I am trying to copy all of the information in column A that starts with
"TC*" to column B. I would like to make this part of a macro but im unsure
how to get excel to select the cells in column A based on this criteria.

What my intended goal is to actually delete all entries in column A that
dont start with "TC*" or "MV*" . The only way I could figure to do this was
to copy them to column B, delete column A and then copy them back.. but if
anyone has a better way to do this please lend me a hand. Thanks!

copy values from one column to another with criteria
 

Try this one for A1:A100 (A1 is the header cell)

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue As String
Dim DeleteValue2 As String
Dim rng As Range

DeleteValue = "<TC*"
DeleteValue2 = "<MV*"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue, _
Operator:=xlAnd, Criteria2:=DeleteValue2
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael A" wrote in message ...
Hello all,
I am trying to copy all of the information in column A that starts with
"TC*" to column B. I would like to make this part of a macro but im unsure
how to get excel to select the cells in column A based on this criteria.

What my intended goal is to actually delete all entries in column A that
dont start with "TC*" or "MV*" . The only way I could figure to do this was
to copy them to column B, delete column A and then copy them back.. but if
anyone has a better way to do this please lend me a hand. Thanks!

copy values from one column to another with criteria
 

Ron, Thanks for your help! That works great!

Im messing with it a little a bit and i have a question. Im trying to add in
a 3rd criteria. "<GFT*" .. However I get an error that tells me wrong # of
agruments. Could you explain how I could do this?

Thanks!



"Ron de Bruin" wrote:

Try this one for A1:A100 (A1 is the header cell)

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue As String
Dim DeleteValue2 As String
Dim rng As Range

DeleteValue = "<TC*"
DeleteValue2 = "<MV*"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue, _
Operator:=xlAnd, Criteria2:=DeleteValue2
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael A" wrote in message ...
Hello all,
I am trying to copy all of the information in column A that starts with
"TC*" to column B. I would like to make this part of a macro but im unsure
how to get excel to select the cells in column A based on this criteria.

What my intended goal is to actually delete all entries in column A that
dont start with "TC*" or "MV*" . The only way I could figure to do this was
to copy them to column B, delete column A and then copy them back.. but if
anyone has a better way to do this please lend me a hand. Thanks!

copy values from one column to another with criteria
 

Hi Michael

Autofilter have a maximum of 2 criteria
You can use Advanced filter to do it but you must enter your criteria in a range.

Or use a loop

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not Left(.Cells(Lrow, "A").Value, 2) = "TC" And _
Not Left(.Cells(Lrow, "A").Value, 2) = "MV" And _
Not Left(.Cells(Lrow, "A").Value, 3) = "GFT" Then .Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael A" wrote in message ...
Ron, Thanks for your help! That works great!

Im messing with it a little a bit and i have a question. Im trying to add in
a 3rd criteria. "<GFT*" .. However I get an error that tells me wrong # of
agruments. Could you explain how I could do this?

Thanks!



"Ron de Bruin" wrote:

Try this one for A1:A100 (A1 is the header cell)

Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue As String
Dim DeleteValue2 As String
Dim rng As Range

DeleteValue = "<TC*"
DeleteValue2 = "<MV*"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue, _
Operator:=xlAnd, Criteria2:=DeleteValue2
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Michael A" wrote in message ...
Hello all,
I am trying to copy all of the information in column A that starts with
"TC*" to column B. I would like to make this part of a macro but im unsure
how to get excel to select the cells in column A based on this criteria.

What my intended goal is to actually delete all entries in column A that
dont start with "TC*" or "MV*" . The only way I could figure to do this was
to copy them to column B, delete column A and then copy them back.. but if
anyone has a better way to do this please lend me a hand. Thanks!