Rezise Range

[Opus]

Can any one tell me why this does not work please.
Runtime error 1004

Sub Ave_Data()
Dim NumRows As Double
Dim Avedata As Range
Set Avedata = Range("A81:M1515")
NumRows = Range("Avedata").Rows.Count
Range("Avedata").Resize(RowSize:=NumRows).Select

End Sub

 

[MickG]

Try:-

Dim NumRows As Double
Dim Avedata As Range
Set Avedata = Range("A81:M1515")
NumRows = Avedata.Rows.Count
Avedata.Resize(NumRows).Select

Mick

 

[Opus]

Thanks mick, I have added two more rows at the botom of the list and the resized range does not select them?

 

[MickG]

Hi, Yes !! you were actually resizing the range to the same size.
Try this:-

Sub Ave_Data()
Dim NumRows As Double
Dim Avedata As Range
Set Avedata = Range("A1:f3")
NumRows = Avedata.Rows.Count
'This resizes the number of Rows x 2
With Avedata
.Resize(.Rows.Count + NumRows).Select
End With
' or , This resizes the number of rows +3
With Avedata
.Resize(.Rows.Count + 3).Select
End With

Mick

 

[Opus]

Thanks Mick that works great!
Is it possible to have the code count the number of rows to select all rows with data weather I've added 2 or more rows.

 

[Norie]

Opus

Why do you want to use Select?

You don't seem to be actually doing anything with the range you are selecting.

Also it's generally not needed to select anything to work with it anyway.

What is the range you actually want to work with? That's not clear, to me anyway, from the last post.

There are various ways to find the last row of data, is that what you want to do?

 

[Opus]

I am trying to clean up my code as it has been very simple to date, I just copy and paste the basics. The code I have is for a KPI that I run for my company and I need to manipulate the info to create a report in print preview.

There are 23 regions and between 45-60 companies to be scored by each region. To date each region has scored twice on each company but some regions have just started to use some companies so they have only one score for said companies. To get to my real problem I need to select the scores or rows that have unique values for three criteria, work out the average of each of the several criteria that have been scored for each company i.e quality service so on, and put this data in a new sheet to show in print preview. If you look at my thread below it may help you to see what I am trying to do.
http://www.mrexcel.com/forum/showthread.php?t=459288