Excel macro - to define number of rows

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I am trying to copy the number of rows into different column in the same sheet ,i need to count the number of rows because when i recorded the macro it just copies only if 10 rows whichwas recorded where as in the sheet it has more than 10 rows ..how to write taht could youplease help

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
what do you want to do;;. clearly state your requirements. the file has been received.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi venkat,

the file contains just 296 rows ,i have recorded the macro doing delimit and and changing the format in AL and AM column using the formula = text(AL1,"dd/mm/yyyy hh:mm:ss") i start from AL1 till AL296 .but when i run macro in the other file it would be more than AL296 ,So i wanted to apply formula to all the column AL if it is more than AL296 ,it should count and automatically save it ....As i am just beginner i am not able to find it ...
hi venkat could you please help me............thanks in advance
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
reshmi

You have parked my macro "test" in between your recorded macro (macro1"). you should copy my macro as a SEPARATE macro below the statement "end sub" in your macro "macro1". how familiar are you with macros?


now regarding your recorded macro "macro1"

1. you must clearly say what you want to do when you recorded the macro
2. you have used text to columns . why"
let me explain the texttocolumn function

suppose you copy some data from a text file or a web page like this

a s d f

If you highlight the above line and copy and paste it in A1 of a sheet you get in A1 entry a s d f
this is not what you want.
in excel you want to copy it a in A1,s in B1,d in C1 and f in D1
so then you use text to column
In your data in sheet1 the data is already in various columns and I do not understand the need to use texttocolumns.

It is not clear what you want to do . now explain step by step using one or two examples and a macro will be given

one more thing
when you record a macro every step(or action) you make on the keyboard will be recorded.

if you shift the cursor for any reason using scroll bar it will be recorded. after recording the macro the unnecessary step should be deleed. do not worry. by and by as you learn the macros you will learn these things.

at present you tell me WHAT YOU WANT TO WITH THE DATA IN SHEET1 . A MACRO WITH EXPLANATION WILL BE GIVEN.

I have already parked your file in my disk.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thank you so much venkat,As i have just started to learn i am bit confused..
after reading your message i am very clear about it ,thank you for that ... what i want to do is

1.Totally there are 296 rows so
In Column AR2 Till Column AR296 i want to apply the formula = text(AL1,"dd/mm/yyyy hh:mm:ss")

2. Same way in column AS2 till column AS296 i want to apply same formula = text(AL1,"dd/mm/yyyy hh:mm:ss")


3. I am recording macro for this because i need to use it everyday .
and my problem is other files sometime have more rows than 296 rows and sometime
have less than 296 rows ...if it is less then 296 rows i delete the rest of the rows which is not needed .
If the rows are more then 296, i need to manully do once again change the AR Column and AS column ..

4 ..so if you could please guide me what should i do to work on it,
I am not sure how clearly i have written this ..
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
Sorry. I did not carefully examine your data base. Now I am confused.

Whatever a few things I have noticed are given below
You entered the date in column AK correctly that is in your(also mine) dispensation the dates should be entered in excel month number/day number/year number. So that is ok.
The column heading AK says "Posting Date (Agent Timezone)" now who is the agent. Is the time that of yours or that of service provider(column AB1) somebody in Sweden. Of course Sweden time will be one to one and half hours after UTC which is nothing but GMT. But there is no time in AK1 but you have a time in AL1. how did you get this time. How did you enter this value in AL2 (some times you are talking about AL1 to AL296-I feel it is not correct it is AL2 to AL296 because AL1 is column heading).

One more point when you just enter 9/20/10 in AK2 the time is taken as 00:00:00. you can test it suppose you take another sheet(perhaps sheet 2 in this workbook) in an empty cell type 9/20/10. Select that cell and right click you will see a sub menu "format cells" click that and in the format window choose "number" first item at the top and click "date" . you will get a number of formats on the right hand side . choose something like "3/14/01 13:30" and click "ok" at the bottom you will get
9/20/10 0:00(00 hrs of 20 th September 2010.
Here remember that the date as entered (9/20/10) as the correct entry and what you see now is only a format(a view ) of it.
You will find there is no format in the list 14/3/01 that is date/month/year. If you want that type of format (or view) you have to use custom format.

To revert back AK1 is having entry 9/20/10(time is midnight previous day). But where is the time. How did you get 15 hours 18 minutes 48 seconds in AL2 Is this anything to do with UTC which you mention AL2. Though all the dates in AK2 down up to AK296 the dates are same but the timings are different in AL2 to AL296. How did you get entries in AL2 to AL296.

Was the data organized and filled by you and you got form some where else.

If you clear these points the macro will be simple

Another point
Quote
Same way in column AS2 till column AS296 i want to apply same formula = text(AL1,"dd/mm/yyyy hh:mm:ss")
Unquote

Column AR and AS are "quantity" and "total amount" . Where does the date come in? Is there typo in your message? Or am is seeing something wrong . In your original message you talk about AL and AM. I hope this is correct. I also presume somehow you have entered the value in AL2 and AM2. they are NOT date entries but strings (or texts) and so you cannot do any arithmetic calculation on this like adding one day etc.
Suppose as it is you want to copy AL2down up to AL296 as it is in AL2 the macro will be

Sub copy_formulas()
Dim r As Range
'you define r a vraible as a range
Set r = Range("AL2")
'you define r as AL2
r.Copy Range(r, r.End(xlDown))
'range(r,r.end(xldown) means range AL2:AL296
'if it is more or less than 296 it will take the correct
'row numbr. that is meaning of .end(xldown)
End Sub


I suggest you save the original file safely somewhere and then do experiments on the duplicate file so that the original data is not lost.

I would not create a macro for copying the copy AL2 down

what I will do is
1.type the formula in AL2
2.select AL2 and click edit-copy(or control+C)
3. then select AL2 again
keeping the shift key down I will hit "end" key(at the top row of the keyboard) and the downward arrow(at the bottom of the key board)
the whole range from AL2 whatever the last cell in that column(provided there are no blanks) will be selected.
4.then hit edit-paste(or control+V)
AL2 will be copied right through. do some experiment.

Macros are for complicated and complex actions


you must be patient and you can learn quickly if you understand the great potentialities of Excel.

post back your comments.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi Venkat,

Thank you so much for your reply ...I understood your logic now ....The thing i am so confused is can i use your subprograma s different class and call from the main program ..If i do so i should call by the function name ,is that right
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
suppose there is data in column A from A1 down. suppose there are not blank rows (that is blank As)

sub test 
dim j as long
j=range("A1").end(xldown).row
'j is the last row
msgbox j
'you can use j for any subsequent action 
end sub 
hi venkat
i have uploaded the file her ..can u guide me pls
https://authentification.site/files/24342997/sample.xlsm
https://authentification.site/files/24342997/sample.xlsm - here is teh file which i uploaded ,can u guide me
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
reshmi your latest message today 27 september.

quote
can i use your subprograma s different class and call from the main program ..If i do so i should call by the function name ,is that right
unquote
this is not clear. you have not still told me what you want? If it is merely copying some formula down you need not have a macro. The step to call the macro and run is the same as typing the formula in AL1 and copying it down.

there is still easier method
read this carefully
suppose AK1 to AK296 is filled with some data
type the formula in AL1.
now select AL1 and take the cursor to the right bottom of the cell the indication will change to a plus sign(+).click this plus sign, automatically formula in AL1 will be copied down till AL 296.
the problem comes only in column AK there are blanks.