UsedRange

[jacqui]

I am using UsedRange to define my range for the subtotals
method, my code is as follows

Dim arr() As Integer
Dim i As Integer

Dim colArray As Variant

Application.StatusBar = "Inserting Sub Totals - Continuing
and Disc values"

Sheets("data").Select

firstcol = 9
LastCol = Range("IV1").End(xlToLeft).Column
ReDim arr(firstcol To LastCol)
For i = firstcol To LastCol
arr(i) = i
Next
colArray = arr()

With ActiveSheet
.UsedRange.Subtotal groupby:=1, Function:=xlSum,
totallist:=colArray, _
Replace:=True
.UsedRange.Subtotal groupby:=4, Function:=xlSum,
totallist:=colArray, _
Replace:=False
End With

Activesheet.UsedRange.select

With Selection

.ClearOutline
.Copy
.PasteSpecial Paste:=xlValues

End With

etc

However, the ClearOutline method does not remove all of my
showlevel buttons because the Grand Totals are outside of
the range. Can anyone kindly explain why this is
happening and how I can resolve this. Note, I have tried
defining a range called "data" based on cells.select
instead of UsedRange but this took forever to process
because the range was looking at every cell on the
worksheet.

Many thanks
Jacqui

 

[Tom Ogilvy]

With Activesheet.Cells
.ClearOutline
.Copy
.PasteSpecial Paste:=xlValues
End With

 

[tcs]

Hi everybody, i got a question related to usedRange too.

i have an excel template which i will pump in some data later.By
default there is only first 2 rows filled with content like header
text.The rest part of the worksheet i did some formatting like
coloring and setting the border type.

My question is wat is the usedRange for row? is it second row or the
last row that is formatted with colors? i wan to know it becoz i will
pump in the data from third row onwards.is a cell without values but
just some formatting consider as used?

i dun wan to hard-coded it becoz different templates used different
number of rows for header section.