none
how to select a column or row RRS feed

  • Question

  • Hi,

    I have a sheet with page set that have A4 size visible area and rest of sheet is hidden. I want it to unhide entire sheet and again hide different area. But entire column or row selection not working. Below is the code, please help me.

    (Actually I want to make many print areas on one sheet. Alternatively showing only one area at a time.)

        Sheets("Sheet1").Select
        Cells.EntireColumn.Hidden = False
        Cells.EntireRow.Hidden = False
        With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .PrintComments = xlPrintNoComments
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperA4
            .BlackAndWhite = False
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
        Application.PrintCommunication = True
        ActiveWindow.View = xlPageLayoutView
        ActiveWindow.DisplayWhitespace = False
       
        With ActiveSheet
       
        'Columns("k:k").Select
        Range("k1").Activate      Activate not working.

        Range(Selection, Selection.End(xlToRight)).Select
        Selection.EntireColumn.Hidden = True
     
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.EntireColumn.Hidden = True

        Rows("57:57").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.EntireRow.Hidden = True
        Range("B7").Select
        End With
        End With

    Friday, July 17, 2015 1:39 AM

All replies

  • Sorry if I'm oversimplifying it, but can't you just record a macro to do this for you?

    Sub Macro1()
    
        Columns("A:J").Select
        Selection.EntireColumn.Hidden = True
        
        Columns("A:K").Select
        Selection.EntireColumn.Hidden = False
    
    
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    Friday, July 17, 2015 1:45 PM
  • Hi ryguy72,

    Thank you,

    I can and I did. It did not work properly when was run through command button. That's why I asked here.

    I wanted this to happen. It works fine now. It is little slow. Any other better way ? I want it to apply this as it is up to last column. Any easy way to this ?

    Private Sub Worksheet_Activate()
            Application.PrintCommunication = False
            Application.ScreenUpdating = False
            With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .CenterHorizontally = True
            .CenterVertically = False
            .BlackAndWhite = False
            ActiveWindow.View = xlPageLayoutView
            ActiveWindow.DisplayWhitespace = False
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
         End With
          
           'PageLayout
            Cells.Select
            With Selection
            .VerticalAlignment = xlBottom
            .Font.name = "Calibri"
            .WrapText = False
            End With

            Columns("B:AP").Select
            With Selection
            Selection.ColumnWidth = 1.795
            End With
       
            Columns("A").Select
            With Selection
            Selection.ColumnWidth = 0.42
            End With
     
            Columns("AQ").Select
            With Selection
            Selection.ColumnWidth = 0.42
            End With
           
            Range("1:1,2:2,4:4,5:5,6:6,7:7,9:9,11:11,13:13,15:15,17:17,19:19,21:21,24:24,26:26,28:28,31:31,34:34,37:37,39:39,41:41,44:44,47:47,50:50,53:53,56:56,58:58,61:61,63:63,65:65").Select
            Selection.RowHeight = 12
            Range("68:68,71:71,73:73,76:76,77:77,78:78,79:79,81:81,82:82,84:84,86:86,88:88,90:90,91:91,92:92,93:93,94:94").Select
            Selection.RowHeight = 12
            Range("8:8,10:10,12:12,14:14,16:16,18:18,20:20,23:23,25:25,27:27,30:30,33:33,36:36,38:38,43:43,46:46,49:49,52:52,55:55,57:57,60:60,67:67,70:70,75:75,80:80,83:83,85:85,87:87,89:89").Select
            Selection.RowHeight = 4
            Range("22:22,29:29,32:32,35:35,40:40,42:42,45:45,48:48,51:51,54:54,59:59,62:62,64:64,66:66,69:69,72:72,74:74").Select
            Selection.RowHeight = 8.25
            Selection.Font.Size = 8

        Range("Q3:AA3").Select
        With Selection
            Selection.RowHeight = 15
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Font.Bold = True
            .name = "Calibri"
            .Font.Size = 13.5
            With Selection.Borders(xlEdgeBottom)
       End With
       End With
     
        Range("Q26:AA26").Select
            With Selection
            Selection.RowHeight = 14
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Font.Bold = True
            .Font.Size = 10.5
            With Selection.Borders(xlEdgeBottom)
       End With
       End With
     
        Application.ScreenUpdating = True
        Application.PrintCommunication = True
    End Sub

    regards

    Saturday, July 18, 2015 7:17 AM
  • I could write

            Columns("A").Select
            With Selection
            Selection.ColumnWidth = 0.42
            End With

            Columns("AQ").Select
            With Selection
            Selection.ColumnWidth = 0.42
            End With

    in single code. Every thing I tried selects columns in between A and AQ too.

             
    Saturday, July 18, 2015 7:20 AM
  • Hi,

    The code you provided could works fine , What do you mean did not work properly, could you please clarify it more clear ?

    By the way, to set a row height, you may could do it like this

    Rows(2).RowHeight = 40

    Best regards,

    lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 20, 2015 3:26 PM
    Moderator
  • it is slow. sheet freezes for some time.

    thank you.

    Monday, July 20, 2015 5:23 PM
  • Hi,

    >>sheet freezes for some time.

    sheets freezes could be many reasons, your logic,the OS, do you have any add-in for excel, what's your version for your Excel ?

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 29, 2015 9:16 AM
    Moderator
  • No add-in , OS Window 7, 32 bit, office 2010 version.
    Wednesday, July 29, 2015 4:52 PM
  • Hello,

    I presume the part where you are setting the page is slowing everything down:

     With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
            .HeaderMargin = Application.InchesToPoints(0)
            .FooterMargin = Application.InchesToPoints(0)
            .CenterHorizontally = True
            .CenterVertically = False
            .BlackAndWhite = False
            ActiveWindow.View = xlPageLayoutView
            ActiveWindow.DisplayWhitespace = False
            .Orientation = xlPortrait
            .PaperSize = xlPaperA4
         End With

    Thursday, July 30, 2015 1:07 PM
  • Can it be done in better way ? with other code ? faster than this .

    regards

    Friday, July 31, 2015 4:43 PM
  • You could test if it is needed to do your pagesetup thing and not do it all the time.

    Also skip the parts that are default ( like  .PaperSize = xlPaperA4 )

    Best regards,

    Wouter

    Monday, August 3, 2015 10:51 AM
  • It can be default value in my pc. But how to test it on another pc ? I have to transfer it to there. I want it in same format. Because there people do not know formatting and get printed many pages in parts rather than a single A4 page. This is problem, I want to solve.

    Please forgive me for ignorance but I am a layman in excel and vba. I try to find here, do testing and modify as per my need it. I do not know how to check default setting in vba. So that vba checks the default settings and run only for which are not default. Making it faster.

    Is there any way, like KEEP THIS FORMATTING FOR THIS FILE EVERYTHERE ? ACROSS OS CHANGE ?

    Regards,


    Thursday, August 6, 2015 3:25 AM
  • Hello,

    set an If-condition before:
       if  .PaperSize <> xlPaperA4 then     .PaperSize = xlPaperA4

    do the same for the other lines. The first time it will take some time and freeze your screen, the second time it will do nothing because your defaults are OK.

      

    Thursday, August 6, 2015 9:31 AM
  • Hi Thank you,

    Now it is pretty fast, only one line creates problem, that I could not manage,

        Cells.Select
        With Selection.Font
        If .name <> "Calibri" Then .name = "Calibri"
        If .ThemeFont <> xlThemeFontMinor Then .ThemeFont = xlThemeFontMinor
        End With
       
        Cells.Select
        With Selection
        If .VerticalAlignment <> xlBottom Then .VerticalAlignment = xlBottom
        'If .ColumnWidth <> 1.799301 Then .ColumnWidth = 1.799301
        If .RowHeight <> 12 Then .RowHeight = 12
        If .WrapText <> False Then .WrapText = False
        End With

    Is there better way to do this ?

    And 'If .ColumnWidth <> 1.799301 Then .ColumnWidth = 1.799301,

    has problem. What is better way to do this ?

    regards

    Friday, August 7, 2015 12:40 PM
  • Hello,

    What problem do you mean?

    Performance problem or run time error?

    If you mean performance: I would suggest you set your columnwidth as one of the last things.

    Hope it helps

    Best regards,

    Wouter

    Monday, August 10, 2015 1:34 PM