number of columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day,
I would like to know if it is possible to set a worksheet to only have let's
say columns A to G and 1 to 10
 
Not really, but you make it look like so.
Format>Column>Hide, same for Rows

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Good day,
| I would like to know if it is possible to set a worksheet to only have let's
| say columns A to G and 1 to 10
 
I would like to know if it is possible to set a worksheet to only
have let's say columns A to G and 1 to 10
Click to expand...

You could do this. Click on the H in column H's header to highlight the
entire column. Next, depress and hold down the Control and Shift keys...
while they are down, press the End key followed by the Right Arrow key. This
will select all the columns from column H to the end of the grid. Next,
click Format/Column/Hide from the Excel menu. Only columns A through G
should remain visible. Now, click the 11 in row 11's header, depress the
Control and Shift keys followed by the End key and Down Arrow key. Click
Format/Row/Hide and you should be left with what you asked for.

Rick
 
I would like to know if it is possible to set a worksheet to only have
let's
say columns A to G and 1 to 10
Click to expand...

Another option might be to place code like this on the specific worksheet
module.
Maybe you can use some ideas here:

Private Sub Worksheet_Activate()
Dim r As Long
Dim c As Long
Dim Nr As Long
Dim Nc As Long
Dim Rng As Range

Set Rng = [A1:G10]

r = Rng.Rows.Count
c = Rng.Columns.Count
Nr = Rows.Count
Nc = Columns.Count

ActiveSheet.Unprotect
ActiveSheet.ScrollArea = vbNullString ' ???
Rng.Locked = False 'Your Choice here ??

Rng.Rows(1).Offset(r).Resize(Nr - r).Hidden = True
Rng.Columns(1).Offset(, c).Resize(, Nc - c).Hidden = True

ActiveSheet.Protect
End Sub
 
You can hide what you don't want to see as others have pointed out.

You can also set the scrollarea to a fixed range.

Since the scrollarea method does not stick between sessions you will have to
reset it each time you open the workbook.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Adjust the sheetname and range to suit.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:G10"
End Sub

Or also in the Thisworkbook module to limit scrollarea on all sheets.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With ActiveSheet
.ScrollArea = "A1:G10"
End With
End Sub


Gord Dibben MS Excel MVP
 
Back
Top