How to tell VBA to stop pasting down a column?

lizsunnysideup

New Member
Joined
Jun 29, 2019
Messages
36
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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