Question on Excel

  PaulOwen 18:12 11 Jun 2004
Locked

How can I explain this!!?? I have a excel document with a lot of data on it, about a 1000 rows. What I need to do is delete all the cells beneath the data because the bar on the right where you arrow down is really tiny and probably goes down to about 5000 rows. How do I enable it just to show the cells with the data in it? Thanks

  VoG II 18:17 11 Jun 2004

You need to reset the used range. Assuming that you have Excel 97 or later:

Open your workbook, ALT+F11 to open the Visual Basic Editor, Insert/Module.

Paste in the following 3 lines of code:

Sub Reset_Range()

ActiveSheet.UsedRange

End Sub

Close the VBE so that you are looking at your worksheet again. Tools/Macro/Macros, click on Reset_Range and click the Run button.

  PaulOwen 18:24 11 Jun 2004

Thanks VoG™ I done as you said but nothings happened!

  VoG II 18:29 11 Jun 2004

Sub Reset_lastcell()

Dim x As Long

x = ActiveWorksheet.UsedRange.Rows.Count

End Sub

  VoG II 18:30 11 Jun 2004

P.S. after running either of these, save the workbook, close it then re-open it.

  PaulOwen 18:40 11 Jun 2004

That doesn't work either.

  PaulOwen 18:42 11 Jun 2004

The last instruction comes up with a box saying Microsoft Visual Basic Run time error '424' end or debug?

  VoG II 18:48 11 Jun 2004

Sub makelastcell()

Dim x As Integer
Dim str As String

Dim xlong As Long, clong As Long, rlong As Long

On Error GoTo 0


str = ActiveCell.Address

Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete

xlong = ActiveSheet.UsedRange.Rows.Count

xlong = ActiveSheet.UsedRange.Columns.Count


Range(Cells(1, ActiveCell.Column + 1), Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete

Beep

xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count

rlong = Cells.SpecialCells(xlLastCell).Row

clong = Cells.SpecialCells(xlLastCell).Column

If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub

ActiveWorkbook.Save

xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count

rlong = Cells.SpecialCells(xlLastCell).Row

clong = Cells.SpecialCells(xlLastCell).Column

If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub

MsgBox "Sorry, Have failed to make " & str & " your last cell"

End Sub

****Before you run this**** click in the last cell in your data (last row, last column).

  PaulOwen 18:58 11 Jun 2004

Thanks for all your help VoG™ but it now comes up with an error saying
Compile Error:

Syntax Error

  VoG II 19:05 11 Jun 2004

Sorry, the second line should be on 2 lines as follows

Dim x As Integer

Dim str As String

  PaulOwen 19:11 11 Jun 2004

Thanks VoG™ your a genius, that seems to have done it. I don't suppose the page can be 'locked' at the bottom because if you arrow down too far all the cells start reappearing (don't want much do I!!)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?