Quick VBA Copy range with data in question

[tezza]

Hi All,

I've done searches but can't quite find the right answer so I've come to you for help.

I'd like to vba code the following:

Count how many rows have data in it (B7:G7 Down with no empty rows but not all Columns filled hense the range)

Copy the data from M7:P7 down the number of rows counted above (This range has formulas in so using something like xldown doesn't appear to work).

Kind Regards
Terry

 

[tezza]

I am not sure what you are talking about.

In your previous post, you said:

And that is exactly what it is doing.

If this is not what you want, then I don't think you are explaining yourself clearly.
Please walk us through an actual example, based on the data you posted, step-by-step.
Tell us EXACTLY what you want to happen, specifically (meaning, tell us EXACT range addresses).

On my sheet it highlights all the way down the sheet ignoring blank rows, I would like it to stop at the last row of data, eg: row 16 if there's nothing below it.

" (meaning, tell us EXACT range addresses)."

I can't give you this as it's dynamic - B7:G (last row of data)

 

[Joe4]

So in columns B:G, below row 16 you have formulas that are returning nothing, right?
This makes it a bit tricky.

What exactly are those formulas?
We may be able to use the range that those formulas use in order to determine the last row (depending on exactly what those formulas look like).

 

[tezza]

So in columns B:G, below row 16 you have formulas that are returning nothing, right?
This makes it a bit tricky.

What exactly are those formulas?
We may be able to use the range that those formulas use in order to determine the last row (depending on exactly what those formulas look like).

Ok, Col B:G is manual data only - no formulas - this is purely to work out how many rows of data there are.

The formulas are in M:P which seems to mess up the xldown or xlup commands.

Whichever way you find easier I need to copy the data from M7:P (Last row of visible data) not beyond.

 

[Joe4]

I thought you said (multiple times) to use columns B:G to locate the last row of data.
And then based on that, copy columns M:P, starting in row 7, down to that last row number.
Is that not correct?

That is what the last code I provided does, provided that there are no formulas in columns B:G that return blank for the entire row below the last row showing data.

 

[tezza]

I thought you said (multiple times) to use columns B:G to locate the last row of data.
And then based on that, copy columns M:P, starting in row 7, down to that last row number.
Is that not correct?

That is what the last code I provided does, provided that there are no formulas in columns B:G that return blank for the entire row below the last row showing data.

See the attached image:

The highlight dotted lines go beyond the bottom row of visible data, in this case it should stop at 12.0 6.0

Otherwise it goes beyond the range of the next sheet.

 

[Joe4]

See the attached image:

View attachment 54991

The highlight dotted lines go beyond the bottom row of visible data, in this case it should stop at 12.0 6.0

Otherwise it goes beyond the range of the next sheet.

OK, I think I see what may be happening.
I think that the Current Region command is extending out past column G.

I cannot tell by looking at your images exactly which column is which, since you cut the column headers off.
Are there some completely blanks columns in there?
If so, which ones?
Do these columns have headers?

It may be helpful to post rows 1-7 for columns A:M, so I can see exactly what it looks like.

 

[tezza]

OK, I think I see what may be happening.
I think that the Current Region command is extending out past column G.

I cannot tell by looking at your images exactly which column is which, since you cut the column headers off.
Are there some completely blanks columns in there?
If so, which ones?
Do these columns have headers?

It may be helpful to post rows 1-7 for columns A:M, so I can see exactly what it looks like.

Here you go:

 

[Joe4]

OK, try this and see if it works:

Sub MyCopy()
    
    Dim rng As Range
    Dim rngLast As Range
    Dim lr As Long
        
'   Find last cell in columns B:G with data
    Set rng = Columns("B:G")
    Set rngLast = rng.Find(What:="*", _
        After:=Cells(rng.Row, rng.Column), _
        LookAt:=xlWhole, _
        LookIn:=xlValues, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious)

'   Find row of that last cell
    lr = rngLast.Row
    
'   Copy range M7:P7 to last row
    Range("M7:P" & lr).Copy
    
End Sub

 

[tezza]

OK, try this and see if it works:

Sub MyCopy()
   
    Dim rng As Range
    Dim rngLast As Range
    Dim lr As Long
       
'   Find last cell in columns B:G with data
    Set rng = Columns("B:G")
    Set rngLast = rng.Find(What:="*", _
        After:=Cells(rng.Row, rng.Column), _
        LookAt:=xlWhole, _
        LookIn:=xlValues, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious)

'   Find row of that last cell
    lr = rngLast.Row
   
'   Copy range M7:P7 to last row
    Range("M7:P" & lr).Copy
   
End Sub

Brilliant, thank you for sticking at it

 

[Joe4]

You are welcome.
Glad we were able to figure it all out!