Max row

VENKYS

Board Regular
Joined
May 19, 2009
Messages
101
I need to process a specific file and the number of rows in the excel differs everytime, while recording a macro, if i am using AUTOFILL and if the data is less than the recorded rows, its working fine. But if the data is higher, the same does not work. What is the way out.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
for example, col A1 has certain values. In col B1 i require 110% of the value in cell A1. example, value in A1=100 say B1 will have a formula =A1*110%

Now the first scenerio, i have a file with 100 records, and if i record the macro giving formula in B1 and then click on AUTOFILL the contents will be autofilled till the end of the record count (as in Col A ie upto A100)

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100%"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B100")
Range("B1:B100").Select

But when i process the next file (say this has 200 records) so if i run the macro, the autofill works upto B100 only. I want the macro to automatically find out the no of records in Col A and execute the command till that range.

How should i go about
 
Upvote 0
Try

Code:
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:B" & LR).FormulaR1C1 = "=RC[-1]*100%"
 
Upvote 0
I don't understand why you are multiplying by 100%, that is the same as multiplying by 1, which will not change the number, but you can also use A1 style references:

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:B" & LR).Formula = "=A1*100%"
End Sub
 
Upvote 0
actually, there is a calculation in place. For sake of easiness i had provided the same as 100%. thanks for your attention and responses.
 
Upvote 0

Forum statistics

Threads
1,224,548
Messages
6,179,453
Members
452,915
Latest member
hannnahheileen

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