How to tell VBA to stop pasting down a column?

lizsunnysideup

New Member
Joined
Jun 29, 2019
Messages
34
Hello. Just a quick question. Is there anyway to tell VBA to stop pasting my data by referring to end of another column for which there is data? I'm trying to paste info down a blank column and have told it to stop at row 200, but the data range always changes. For instance the end row may be 159 one day, 121 the next, etc. I set the code to 200 because it seems there are never more than 200 rows, but I'm not sure how set the range if it's constantly changing. Here's some of the code I have now.

Rich (BB code):
Rich (BB code):
   Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-1]<=14), TRUE)"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI200")
    Range("AI2:AI200").Select
    Rows("1:1").Select
    Range("O1").Activate
    Selection.AutoFilter
    End Sub


And for reference if it's possible to end pasting in column AI using column A because column A will always have a last active cell with data in it.

Thanks
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Change this part of your Range statements (although I am not sure why you are selecting the range after the AutoFill given you then turn around and select Row 1)...

"AI2:AI200"

to this...

"AI2:AI" & Cells(Rows.Count, "A").End(xlUp).Row)
 
Upvote 0
Or as the formula is looking at column AH
Code:
Range("AI2&AI" & Range("AH" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-1]<=14), TRUE)"
Rows("1:1").AutoFilter
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top