Macro help needed

[gabriellejayde]

I am not great with macros and can't figure this one out. This is the current macro we use to copy a cell range:

Sub CopyInventory()
'
' CopyInventory Macro
' FROM INVENTORY MANAGER

'
Range("F33:O5000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub

So it's copying almost 5000 rows. column F is filled with a formula down to row 5000, but often we only have a few hundred rows of columns G-O filled. Rather than always copying down to row 5000, I'd like to only copy columns F through O down to the last filled row in column G. (because column F is filled with a formula).

Does anyone know how to do this?

 

[ranman256]

Dim r As Long

Range("G1").Select
Selection.End(xlDown).Select
r = ActiveCell.Row
Range("F1:O" & r).Select
Selection.Copy

 

[Anthony47]

Your Range(Selection, Selection.End(xlDown)).Select indeed select up to the latest row in the worksheet (1 Mill; much worse than 5000)
So if you remove that line things will be much better.

You can copy the populated rows only by using, for exampèle:

LR = Evaluate("MAX((F33:O5000<>"""")*ROW(F33:O5000))")
Range("F33:O" & LR).Copy

This in addition to what already suggested by RanMan, above

 

[gabriellejayde]

Dim r As Long

Range("G1").Select
Selection.End(xlDown).Select
r = ActiveCell.Row
Range("F1:O" & r).Select
Selection.Copy

Thanks... but that copied the whole workbook. I just want to copy columns F-O, from line 33 down to the last filled line in column G. If I don't specify which column, I'll get the whole spreadsheet because column F is filled with a formula down to line 5000.

 

[Anthony47]

Did you test the other suggestion?

 

[gabriellejayde]

Did you test the other suggestion?

Hi. i just did it and it worked. it selects the bottom cell in the spreadsheet, but it's copying the right info so it's fine. Thank you for your help!!

 

[Anthony47]

Hi. i just did it and it worked. it selects the bottom cell in the spreadsheet, but it's copying the right info so it's fine. Thank you for your help!!

Thank you for the feedback, but my code don't select any cell...