copy rows to other sheet
 

Hi ,

I have a data with about 4500 rows and 12 columns. I would like to
copy only the rows where column C haven't "0" value.can someone hep me
with a macro ?


Example

Thank you

copy rows to other sheet
 

Hi,

Because you put 0 in quotes i'm not sure if you meant zero or the letter O
so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor
insert a module and paste this in and try it:-

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow)
For Each C In MyRange
If C.Value < 0 Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub


Mike


"Carpe Diem" wrote:

Hi ,

I have a data with about 4500 rows and 12 columns. I would like to
copy only the rows where column C haven't "0" value.can someone hep me
with a macro ?


Example

Thank you

copy rows to other sheet
 

Hi Mike,

It worked just like I need, however is it possible to verify to
statements ? Column C < 0 (zero) and Column S <0 ?



On Dec 17, 5:16 pm, Mike H wrote:
Hi,

Because you put 0 in quotes i'm not sure if you meant zero or the letter O
so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor
insert a module and paste this in and try it:-

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow)
For Each C In MyRange
If C.Value < 0 Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Mike



"Carpe Diem" wrote:
Hi ,

I have a data with about 4500 rows and 12 columns. I would like to
copy only the rows where column C haven't "0" value.can someone hep me
with a macro ?

Example

Thank you- Hide quoted text -

- Show quoted text -

copy rows to other sheet
 

maybe this

If c.Value < 0 And c.Offset(0, 16).Value < 0 Then

Mike


"Carpe Diem" wrote:

Hi Mike,

It worked just like I need, however is it possible to verify to
statements ? Column C < 0 (zero) and Column S <0 ?



On Dec 17, 5:16 pm, Mike H wrote:
Hi,

Because you put 0 in quotes i'm not sure if you meant zero or the letter O
so this assumes zero and copies sheet 1 to sheet 2. Alt+F11 to open VB editor
insert a module and paste this in and try it:-

Sub copyit()
Dim MyRange, MyRange1 As Range
Sheets("Sheet1").Select
LastRow = Sheets("Sheet1").Range("C65536").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("C1:C" & LastRow)
For Each C In MyRange
If C.Value < 0 Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
MyRange1.Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Mike



"Carpe Diem" wrote:
Hi ,

I have a data with about 4500 rows and 12 columns. I would like to
copy only the rows where column C haven't "0" value.can someone hep me
with a macro ?

Example

Thank you- Hide quoted text -

- Show quoted text -