Copy rows where entry in column A

[access user]

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

 

[access user]

Just to modify this - the test for column A only needs to extend from row 2
to row 100. So if, in those set of rows, there is a value in column A then
copy the antire row (values only) to another sheet.

James

 

[Mike H]

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

 

[Joel]

Your original request

Sheets("Sheet1").Cells.SpecialCells(xlTextValues).EntireRow.Copy
Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValues


fror a limited range
Sheets("Sheet1").Range("A1:A100").SpecialCells(xlTextValues).EntireRow.Copy
Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValues

 

[access user]

Hi

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

on line copyrange.copy

tia
James

 

[Mike H]

Thats because it's not finding any cells with data in so there is nothing for
it to copy. I never bothered to trap for that becuase I assumed incorrectly
there would always be something. You can trap for that with

If Not copyrange Is Nothing Then
copyrange.Copy
Else
Exit Sub
End If

Mike

 

[access user]

You deserve a pint of Guiness because you are 'pure Genius' and it's St
Patrick's Day. Thanks a lot.
James

 

[access user]

Thanks Joel, Mike H got there first. Appreciate your response though.
James

 

[Mike H]

Thank's for that but three small points:-

I'm not a genius far from it.
Joel's solution is better than mine
I'm not going to enter the debate about when St Patricks day is this year
because the Pope seems to have quite strong views in the matter<g>

Mike

 

[access user]

Ha ha. Can't comment on the first point (let's just say it's relative).
On point 2 - I agree Joel's solution looks much neater but (pardon me if I'm
wrong, as I'm not a coder) it does not appear to make the test for 'where
there's a value in column A'. probably quite easy to add that though.
Point 3 - Google has God status does it not? Take a look...they seem to
think it's today.

cheers
James

 

[Joel]

I'm not a genius. The 247th St Patrick Day parade is going to start today in
NYC marching down 5th avenue like normal on the 17th Of March.

 

[access user]

Thanks Joel - sounds pretty conclusive...

I'm not a genius. The 247th St Patrick Day parade is going to start today in
NYC marching down 5th avenue like normal on the 17th Of March.