Clear Sheet2 before copy

  • Thread starter Thread starter RayD
  • Start date Start date
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
 
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 **
 
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.
 
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...
 
Back
Top