Hi All

I have a list of data that has been "dumped" from another system int
Excel, that fills Columns A thru N. I need to extract from this al
rows that have the number 1 in column G, however I cannot seem to b
able to correctly define my criteria for this to work as I keep gettin
a Reference is not valid error

Is what I am trying to do even possible ?

Thanks in advance

Kare

--
ks
-----------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...nfo&userid=626
View this thread: http://www.excelforum.com/showthread.php?threadid=48808

give this a try. it assumes your data is on sheet1 and you want it extracted
to sheet2

Option Explicit
Sub Extract()
Dim rng As Range
Dim cell As Range
Dim lastrow As Long
Dim firstrow As Long
firstrow = 2
lastrow = Cells(Rows.Count, "G").End(xlUp).Row
With Worksheets("sheet1")
Set rng = Range("g1:g" & lastrow)
For Each cell In rng
If cell.Value = 1 Then
.Range("A" & cell).EntireRow.Copy
Worksheets("sheet2").Range("A" & firstrow).PasteSpecial xlValues
firstrow = firstrow + 1
End If
Debug.Print cell.Row
Next cell
End With
Application.CutCopyMode = False
End Sub

--


Gary


"ksp" wrote in message
...

Hi All

I have a list of data that has been "dumped" from another system into
Excel, that fills Columns A thru N. I need to extract from this all
rows that have the number 1 in column G, however I cannot seem to be
able to correctly define my criteria for this to work as I keep getting
a Reference is not valid error

Is what I am trying to do even possible ?

Thanks in advance

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile:
http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=488087

sorry, had a typo on this line, for the . row after cell.

..Range("A" & cell.Row).EntireRow.Copy

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
give this a try. it assumes your data is on sheet1 and you want it
extracted to sheet2

Option Explicit
Sub Extract()
Dim rng As Range
Dim cell As Range
Dim lastrow As Long
Dim firstrow As Long
firstrow = 2
lastrow = Cells(Rows.Count, "G").End(xlUp).Row
With Worksheets("sheet1")
Set rng = Range("g1:g" & lastrow)
For Each cell In rng
If cell.Value = 1 Then
.Range("A" & cell).EntireRow.Copy
Worksheets("sheet2").Range("A" & firstrow).PasteSpecial xlValues
firstrow = firstrow + 1
End If
Debug.Print cell.Row
Next cell
End With
Application.CutCopyMode = False
End Sub

--


Gary


"ksp" wrote in message
...

Hi All

I have a list of data that has been "dumped" from another system into
Excel, that fills Columns A thru N. I need to extract from this all
rows that have the number 1 in column G, however I cannot seem to be
able to correctly define my criteria for this to work as I keep getting
a Reference is not valid error

Is what I am trying to do even possible ?

Thanks in advance

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile:
http://www.excelforum.com/member.php...fo&userid=6267
View this thread:
http://www.excelforum.com/showthread...hreadid=488087

Hi Gary

Thanks for your script, it sort of works, it certainly recognises that
there are 8 rows that meet this criteria in my test data, but it is
copying Row 1 eight times to sheet 2 instead of the row that the 1
resides in

Are you able to help ?

Thanks

Regards

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=488087

did you try the change i made in the 2nd post?

--


Gary


"ksp" wrote in message
...

Hi Gary

Thanks for your script, it sort of works, it certainly recognises that
there are 8 rows that meet this criteria in my test data, but it is
copying Row 1 eight times to sheet 2 instead of the row that the 1
resides in

Are you able to help ?

Thanks

Regards

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile:
http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=488087

Hi Gary

Thanks, that works perfectly now

Thanks for your help - much appreciated

Ta

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=488087

you're welcome

--


Gary


"ksp" wrote in message
...

Hi Gary

Thanks, that works perfectly now

Thanks for your help - much appreciated

Ta

Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile:
http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=488087