Hiding rows in a function

[Aero]

I have a Worksheet and the first 49 rows have data on it,
now i want to hide rows 50 to the end of the document, but cant seem to figure this out...

Right now i'm trying this: (h = 7)



Dim n As Integer
n = Cells.SpecialCells(xlCellTypeLastCell).Row
k = 7 * h + 1
Rows("k:n").Hidden = True

[Bob Phillips]

Try



Rows(k & ":" & n).Hidden = True

[Aero]

Hmm,

now it hides all the rows 1 untill 50, when all 50+ rows should be hidden...

I might have to be some more specific, this is what i do :



Function tabledim(ws As Worksheet, h As Integer, w As Integer)
Dim i As Integer
Dim k As Integer
Dim n As Integer
n = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To 7 * h
Rows(i).RowHeight = 7.5
Next i
k = 7 * h + 1
Rows(k & ":" & n).Hidden = True
End Function

(The first 50 rows will have data later on)

EDIT: i think i'm seeing 'n' wrongly, it probably doesn't mean 'end of document' ?

[Bob Phillips]

Stop the code on the last line and see what k & n contain.

[Aero]

n = 1
and k = 50

So indeed, it hides row 1 until 50.

What do i need to do to hide rows after row 50?

[Bob Phillips]

That suggests to me that there is no data in the workbook. Can you post it?

[Aero]

That suggests to me that there is no data in the workbook. Can you post it?

No indeed, (said that under the code i posted before)

Basically so far it's just the function i posted above and a subprocedure to test it the function..

(if you might be wondering why i'm not just inserting the data first, its because we have to follow the steps in order to make the whole 'task')

[mdmackillop]

If n <50 then n = 50
Rows(k & ":" & n).Hidden = True

[Aero]

If n <50 then n = 50
Rows(k & ":" & n).Hidden = True


That gives me a 'Type mismatch' error, even if i append End if to the code,
am i supposed to put it somewhere special ?

[mdmackillop]

Try < 50 instead of <50

[Bob Phillips]

If there is no data, then there is no end of document, so what do you expect to hide.

[Aero]

Try < 50 instead of <50

I'll try that as soon as i get home


If there is no data, then there is no end of document, so what do you expect to hide.

So as long as i don't have any data there is no way i can hide rows 50+ ?

This is a visual of how it should become, just that there is no data yet in the cells, as you can see the rows after row 49 are not visible..

http://i44.tinypic.com/i74uht.jpg

[Bob Phillips]

Yes of course you can still hide it, but as there is no data, there is no end of data, so there is no way of knowing where to stop.

Or do you want to hide them all except 50? If so, maybe



Rows(k & ":" & Rows.Count - k + 1).Hidden = True


BTW, why would you use Excel for a periodic table?

[Aero]

I want to hide row 50, 51, 52, 53, ...
Like the small screenshot i posted, the space is grey there, no rows are shown...

It's not my choice to make this in Excel, we have to create this table using VBA (we wont insert the data manually), it's sort of like my 'homework' ...

[Bob Phillips]

Did you try my last suggestion?

[wizard7070]

Use the following. If you were to put this in a new workbook. Then what it will do is hide all the rows after 50. (grey them out)


Public Sub hide()
Dim r As Long
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("sheet1")
For r = 50 To 65536
wks.Cells(r, 1).EntireRow.Hidden = True
Next r
End Sub

If your using office 2007 then replace 65536 to whatever the number of rows it can store.

[Aero]

I'm home now and yes, i tried it and it works. :) Didn't know it would be something that easy.


Rows(k & ":" & Rows.Count).Hidden = True
Also, on the Type Mismatch error, i think it was because i pasted the code before i initialised 'k'.
Anyways it works now, so thanks :)

EDIT: wizard7070, i think that also works since the principle is the same, the solution right now is shorter thou, but thanks for the help anyway !

[mdmackillop]

Hi Wizard
Welcome to VBAX
You really don't want to loop over a million rows!
Regards
MD

ps Tested a loop overnight, just out of interest. It took 4.75 hours



wks.Range(Rows(50), Rows(Rows.Count)).Hidden = True