Macro Record with varying rows

[xyz1010]

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?

 

[Ruddles]

This snippet of code looks for the last piece of data in a particular column and stores the row it's found in:-

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':-

for x = 4 to lastrow

range("a4:z" & lastrow).copy

msgbox "your data ends at row " & lastrow

Does that help?

 

[xyz1010]

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]

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:-

[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]

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]

Start typing =AV in a worksheet cell and mysteries will unfold...

 

[xyz1010]

I've tried that but it gives me a Cellular Reference error message ...

 

[Ruddles]

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?