Consulting

Results 1 to 18 of 18

Thread: Hiding rows in a function

  1. #1

    Hiding rows in a function

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try

    [vba]

    Rows(k & ":" & n).Hidden = True
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    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' ?
    Last edited by Aero; 05-04-2010 at 11:04 AM.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Stop the code on the last line and see what k & n contain.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    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?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That suggests to me that there is no data in the workbook. Can you post it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Quote Originally Posted by xld
    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')

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    If n <50 then n = 50 
    Rows(k & ":" & n).Hidden = True
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Quote Originally Posted by mdmackillop
    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 ?

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try < 50 instead of <50
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    If there is no data, then there is no end of document, so what do you expect to hide.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Quote Originally Posted by mdmackillop
    Try < 50 instead of <50
    I'll try that as soon as i get home

    Quote Originally Posted by xld
    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..


  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    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' ...

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Did you try my last suggestion?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    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.

  17. #17
    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 !

  18. #18
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •