What is the most efficient (fastest) way to hide unused col/row

[MCubitt]

Using VBA, given a start column and row number, can I hide the remaining columns/rows quickly?

Eg. On sheet1, I am using columns 1-8, rows 1 to 90.

I want to hide the remaining cells (columns 9 to whatever IV is and rows 91 to 65536).

Note, I am "creating" the sheet in VBA so a method of hiding it all and then widening those that ARE used would be useful too.

Thanks



Applications Support
UK

 

[MCubitt]

Similarly, I am applying validation (allows pull down X or space) on many cells, in 8 columns, 2000 rows. It takles AGES to do that. Is there a speedy way?

I am working column by column


Applications Support
UK

 

[Loomah]

Hi
For the first question this should work

Sub a()
Dim iCol As Integer
Dim lRow As Long
    iCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1
    lRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
    Rows(lRow & ":65536").Hidden = True
    Columns(iCol & ":256").Hidden = True
End Sub

but I was having problems with the columns (just moved back to xl97, dunno if that impacts!!?)

For the second part highlight the whole range you want to use and apply the validation rules to that range

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[MCubitt]

Thanks Loomah, I am using Excel 2000.

I was using this appraoch:

  '  Hide remaining columns (up to IV)
  For x = placedcol To 256
    Sheets("Users to add to core").Columns(x).ColumnWidth = 0
  Next x
  
  ' Hide remaining rows
  For x = Cells(18, 1) + 1 To 65536
    Sheets("Users to add to core").Rows(x).RowHeight = 0
  Next x

Cells(18,1)=last row used (one before 1st to hide)
placedcol = next column (1st to hide)

So it wasn't the "which is the last used" but the hiding that I was after.

But you gave me the answer, use a range, not individuals!
So I have:

  '  Hide remaining columns (up to IV)
  Sheets("Users to add to core").Columns(placedcol & ":256").Hidden = True
  
  ' Hide remaining rows
  Sheets("Users to add to core").Rows(Cells(18, 1) + 1 & ":65536").Hidden = True

Much better!

For the validation, I had my sheet creation working column by column, so it would add validation to each column individually.

I realise now that once the final column to have the validation has been "created" I can simply do a range.

However, this doesn't work - I get an error:

                With Worksheets("Users to add to core").Columns(placedcol & ":" & placedcol + numrecs).Validation
                  .Delete
                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=_COLX"
                  .IgnoreBlank = True
                  .InCellDropdown = True
                  .InputTitle = ""
                  .ErrorTitle = ""
                  .InputMessage = ""
                  .ErrorMessage = ""
                  .ShowInput = True
                  .ShowError = True
                End With

It's an error 1004, do with my selection.

I was previously using:
With Worksheets("Users to add to core").Columns(placedcol).Validation

Any ideas, please??


Applications Support
UK

 

[MCubitt]

Well, managed to resolve it..
With Worksheets("Users to add to core").Range(placed1 & "2:" & placed2 & Cells(18, 1)).Validation

Now, placed1 amd placed2 are A, B, C... representing the column.

Seems to work.


Applications Support
UK