asked on

Select a range for copy

Hello, the following selects a range, if it exists, whether there is only one, or a bunch. However it has a row = 2 in it. How would I rewrite this to write that out of it? It sort of confuses me. If found it in a google search. Its like saying, if the cell in row 2 is blank, the activecell (which is k4) is blank too?

row is used 3 times in this in this sub. How can I do the same thing without using this row that dim as Integer??
Looking for some assistance rewriting this. Please advise and thanks.

    Range("K4").Select
    If ActiveCell = "" Then
        MsgBox ("No Config Profiles found to ALE")
        Exit Sub
    Else
        Dim row As Integer
        Dim value As String
        row = 2
        Do
           value = Cells(row, 11).value ' 11=column K
           If value = "" Then
              Exit Do
           End If
           row = row + 1
        Loop
        Range("K4:K" & (row - 1)).Select
    End If
    Selection.Copy

Open in new window

Subodh Tiwari (Neeraj)

Does this work for you?

Dim lr As Long
lr = Cells(Rows.Count, 11).End(xlUp).Row
If lr > 4 Then Range("K4:K" & lr).Copy

Open in new window

RWayneH

ASKER

Yes this works on the columns that do not have data under the selection area. The datasets are not very big, so I am not worried about speed. Is there something that I could use when there is data under the selection area? This is one reason why I selected it from the top down instead of bottom up. I have about 8 columns that your suggestion will work on, but 3 that it will not.

Subodh Tiwari (Neeraj)

Do you mean there maybe blanks down the rows?
What do you mean by selection area?
Can you upload a sample workbook with some dummy data in it and highlight the cells you want to copy? That way it would be easy to visualize your requirement more clearly.

RWayneH

ASKER

In this example we are using column K, It assumes no data in that column except what you are selecting right? So what if data is in K4 thru K15 only, it will pick that. but if data is in K4 thru K15 and K20 thru K30, it will select the copy range of K4 thru K30, when all we want is K4 thru K15. I am trying to use this on different columns some columns have data further down the column were this will not work.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RWayneH

ASKER

Yes, but option 2 does not work if there is a single row of data in K4 only.
Thanks for your help with this, it helped me complete my select copy area. This one uses column H instead of K but same thing.

I landed on this and it works pretty good. Thanks for the help.

Range("H4").Select
If ActiveCell = "" Then
    MsgBox ("No Matls found to ALE")
    Exit Sub
ElseIf ActiveCell.Offset(1, 0) = "" Then 'only one there
    Selection.Copy
Else
    Dim lr As Long
    lr = Range("H4").End(xlDown).row
    Range("H4:H" & lr).Select
    Selection.Copy  'needed whole range to copy, I chg cell background later
End If

Open in new window

RWayneH

ASKER

Appreciate the help.

Subodh Tiwari (Neeraj)

You're welcome!