Resetting the usedrange

[shavenlunatic]

Hi,

I get sent a HUGE excel daily.. huge mainly due to the fact that a couple of sheets usedrange is set to 65000+ rows

now, i tried running

Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange

but I have a feeling (just by looking at it, as i've not used specialcells before and not 100% sure on it) that it is just checking if the cell in the first column is blank then delete the WHOLE row..

this would be great but occasionally the first cell will be blank but the rest of the row may still contain data. Plus I wasn't to bothered about deleting the occasional blank row it was just the blanks from the end-of-data to the max row that I wanted rid of.

I could do this by looking backwards through the sheet and deleting the rows as I go until I hit some data, but I just wanted a 1 or 2 line chunk of code I can stick in the immediate window and run. Any ideas

Hope I explained myself well enough, still haven't had my coffee injection.

Thanks in advance

________
clueless

 

[SkipVought]

Hi,

I'm confused. Why are you deleting ANY rows? You're basically saying, if there's anything in the row, don't delete it. Otherwise, there's nothing in the row!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

Depends on the reason that the usedrange is so big.
It could be formatted or there could've been formulae in there which returned a "" result

either way this should pretty much do what you need

lRow = cells(columns(1).cells.count,1).end(xlup).row

rows(lrow & ":" & columns(1).cells.count).entirerow.delete

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

If you're looking at the UsedRange...

with activesheet.usedrange
  lFirstRow = .row
  iFirstCol = .column
  lLastRow = .rows.count + lFirstRow - 1
  iLastCol = .columns.count + iFirstCol - 1
end with

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[PHV]

or 2 line chunk of code I can stick in the immediate window and run
Doesn't this suffice ?
For Each sh In ActiveWorkbook.Worksheets: sh.[A1].Select: sh.UsedRange: Next: ActiveWorkbook.Save

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shavenlunatic]

this:

lRow  = cells(columns(1).cells.count,1).end(xlup).row
rows(lrow & ":" & columns(1).cells.count).entirerow.delete
Activesheet.UsedRange

does exactly what i needed. Thanks very much

________
clueless

 

[shavenlunatic]

PHV, I replied before your reponse was posted.. I will have a play at that too.. cheers

________
clueless