Macro Record with varying rows

xyz1010

New Member
Joined
Jul 11, 2011
Messages
6
Hello All,

I'm new to Macros and VBA but I need it to get things done. I'm using both Macros Record and the count feature to get the number of entries I have in my worksheet. The problem is that my rows are constantly changing, depending on the database I get my information from. Also, I want to start counting from A4 to wherever my data might end. That's where my Macros don't work. Any help please?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
This snippet of code looks for the last piece of data in a particular column and stores the row it's found in:-
Code:
dim lastrow as long
with thisworkbook.sheets(1)
  lastrow=.cells(.rows.count,"a").end(xlup).row
end with
(That would be for the first sheet in the current workbook: change the With statement if you want it to apply to a different sheet.)

Then you use lastrow whenever you need to refer to 'the last row of data':-
Code:
for x = 4 to lastrow
Code:
range("a4:z" & lastrow).copy
Code:
msgbox "your data ends at row " & lastrow
Does that help?
 

xyz1010

New Member
Joined
Jul 11, 2011
Messages
6
Hello and thank you for your help. I copied the code you gave me but it gives an error message "invalid outside procedure" maybe its my lack of proper experience that's contributing to this error. Also I would like to take the average, given that there's also varying rows depending on my data. How do I go about it? Any help will be appreciated. Thank you
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Those are only code snippets - demonstrations of the way those commands operate. You need to incorporate them into the coding of your actual program where you need them.



For example, if you wanted to calculate the average of the values in column A starting from row 4 and going down as far as the data extends, you would have to:-
  • find where the end of the data was
  • create a variable in which you'd keep a running total of the values
  • loop through the values from row 4 to the end of the data, adding them to the variable as you go
  • divide the variable by the number of values, i.e. the number of rows minus the first three, to obtain the average
  • display the result
Some of the snippets of code I posted will do some of those things, so you'd use those where appropriate (highlighted in red) and then add your own code to do 'the rest'.

I would have produced something like this:-
Code:
[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub CalcAverage()[/FONT]
 
[FONT=Fixedsys][COLOR=red]Dim lastrow As Long[/COLOR][/FONT]
[FONT=Fixedsys]Dim dtotal As Double[/FONT]
[FONT=Fixedsys]Dim x As Long[/FONT]
[FONT=Fixedsys]Dim daverage As Double[/FONT]
 
[FONT=Fixedsys][COLOR=red]With ThisWorkbook.Sheets(1)[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=red]  lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row[/COLOR][/FONT]
[FONT=Fixedsys][COLOR=red]End With[/COLOR][/FONT]
 
[FONT=Fixedsys][COLOR=red]For x = 4 To lastrow[/COLOR][/FONT]
[FONT=Fixedsys]  dtotal = dtotal + Cells(x, 1).Value[/FONT]
[FONT=Fixedsys]Next irow[/FONT]
 
[FONT=Fixedsys]daverage = dtotal / (lastrow - 3) [COLOR=green]' because we don't use rows 1-3[/COLOR][/FONT]
 
[FONT=Fixedsys][COLOR=red]MsgBox[/COLOR] daverage[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]
In fact I wouldn't have produced this at all since there's a worksheet function which can do this, so I would have used that rather than delve into VBA. Even if I'd been obliged to do it via VBA, I would still have called the worksheet function to do it for me.
 

xyz1010

New Member
Joined
Jul 11, 2011
Messages
6

ADVERTISEMENT

Wow! Thank you so much!!! it worked!!! I'm new to excel and the whole VBA language so thank you very much! You mentioned that there was an excel function that can take care of it ... can you give anymore insight on it please? Thank you!!
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Start typing =AV in a worksheet cell and mysteries will unfold... :)
 
Last edited:

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Don't hit Enter - go to an empty cell and press =, then A, then V.

Do you get a popup listing all the possible functions beginning with AV and a description of what's required to use them?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,121
Messages
5,526,948
Members
409,730
Latest member
mimipiz

This Week's Hot Topics

Top