Hi

I get 'object variable or With block variable not set error'

on line copyrange.copy

tia
James

"Mike H" wrote:

Alt + F11 to open VB editor. Right click 'This workbook' and insert module.
Paste this in and run it

Sub stance()
Dim myrange, copyrange As Range
Sheets("Sheet1").Select
Set myrange = Range("A2:A100")
For Each c In myrange
If c.Value < "" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If

End If
Next
copyrange.Copy
Sheets("Sheet2").Select 'Change to suit
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Did that work?

Mike

"access user" wrote:

Hi

I wish to copy only rows where there is an entry in column A. For those
rows, I wish to copy the entire row (values only) to another sheet.

tia
James