Clear Sheet2 before copy

R

RayD

Hello,

I had help earlier this morning with this macro, it is working well
now, but I was wondering if it is possible to incorporate a function
that when first running the macro it will clear/delete Sheet2 "ROWS"
starting row 2 through row 2000, also returning the row height to it
default, column width is OK. Than complete the task. -Many Thanks


Sub Macro1()
For Each c In [H5:H2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 3 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub
 
P

Paul B

Ray, add this

Sheets("Sheet2").Range("2:2000").Clear
Sheets("Sheet2").Range("2:2000").RowHeight = 12.75

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
G

Guest

Hi you can simply record a macro by using the macro tool.
something like this:

Function DeleteRows()
Rows("1:2000").Select
Selection.Delete Shift:=xlUp
Range("A1").Select 'optional if you want Cell "A1" to
be selected.

End Function

Call this function before 'For Loop' and it should do the
trick.

Regards.
 
R

RayD

Many Thanks..



Hi you can simply record a macro by using the macro tool.
something like this:

Function DeleteRows()
Rows("1:2000").Select
Selection.Delete Shift:=xlUp
Range("A1").Select 'optional if you want Cell "A1" to
be selected.

End Function

Call this function before 'For Loop' and it should do the
trick.

Regards.
-----Original Message-----
Hello,

I had help earlier this morning with this macro, it is working well
now, but I was wondering if it is possible to incorporate a function
that when first running the macro it will clear/delete Sheet2 "ROWS"
starting row 2 through row 2000, also returning the row height to it
default, column width is OK. Than complete the task. - Many Thanks


Sub Macro1()
For Each c In [H5:H2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 3 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub
.
Click to expand...
Click to expand...
 
Top