IF range is blank End IF

I am using the following code to find and then select a range, however, I if the range is blank I need to ignore and move on to the next. Would appreciate it if someone could provide me with the code I need to say 'IF range is Blank move to next piece of code.

Sheets("January2013").Select
        Range("B:B").Find("Book", , xlValues, xlWhole, , , False).Select
        ActiveCell.Offset(1, 10).Select
        Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
        Selection.Copy
       
        Sheets("January2014").Select
        Range("P2").Select
        ActiveSheet.Paste

Many Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
It's not quite clear what you want to skip, but I'm assuming you want to look at the value in the next row, column L, and only do the paste to the next sheet if it is not blank.  If that's correct, try this code:
        Sheets("January2013").Select
        
        Range("B:B").Find("Book", , xlValues, xlWhole, , , False).Select
        ActiveCell.Offset(1, 10).Select
        
        If Not IsEmpty(ActiveCell.Value) Then
            Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
            Selection.Copy
            
            Sheets("January2014").Select
            Range("P2").Select
            ActiveSheet.Paste
        
        End If

Open in new window

0
Saqib Husain, SyedEngineerCommented:
I think it should be something like this. I have reduced the "selection"s from the code

Sheets("January2013").Select
        set bookr = Range("B:B").Find("Book", , xlValues, xlWhole, , , False)
        if not bookr is nothing then
                set bookr=bookr.Offset(1, 10)
                Range(bookr, Cells(Rows.Count, bookr.Column).End(xlUp)).Copy  Sheets("January2014").Range("P2")
        endif

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
ssaqibh these things always work out more difficult than I first thought. I thought your solution worked but, when I have put it into my actual file........ it doesn't. It could be that the header row in my file is in row 10 but I am not sure. The word that it is looking for 'Book' is in row 13 [unlucky for me but it could be in 5 or 15 etc] but, when I find the word and the row it is on I do not want to copy anything that is above that cell. so, this is what I want to do:

find the word 'Book' it's in row 13, offset(1, 10) and then copy anything from that cell and below.
do not copy above row 13.

Can you help?
0
JagwarmanAuthor Commented:
to clarify.....It could be that the header row in my file is in row 10 but I am not sure.

should read: header row in my file is in row 10.
0
Saqib Husain, SyedEngineerCommented:
Apparently the problem occurs when there is nothing in that cell and below. Try removing the .end(xlup) part.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.