excel macro - to define number of rows Closed

Question

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

Configuration: Windows Vista / Internet Explorer 7.0

venkat1926 · Answer

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

reshmi · Answer

https://authentification.site/files/24342997/sample.xlsm - here is teh file which i uploaded ,can u guide me

venkat1926 · Answer

what do you want to do;;. clearly state your requirements. the file has been received.

venkat1926 · Answer

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.

venkat1926 · Answer

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.

venkat1926 · Answer

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.

Excel macro - to define number of rows

6 responses

Similar discussions

Subscribe To Our Newsletter!