ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Code to Hide and Unhide Rows and Columns in Excel

Updated on September 4, 2012

Hiding a row or column makes it invisible. The column still exists; it just can't be seen.

Hiding a row or column is roughly equivalent to setting its respective height or width to zero.

As you begin to develop complicated Excel spreadsheets you will find many reason for hiding columns and rows from the end user.

Hiding Columns in Excel

To hide a column:

  • Select a column or series of columns
  • Right click
  • Select Hide

Hiding Columns "B" through "D"

Unhiding Columns in Excel

To Unhide Columns:

  • Select the range of columns that contains hidden columns
  • Right Click
  • Click Unhide

Unhiding Columns "B" through "D"

Hiding and Unhiding Rows

Hiding and Unhiding Rows works that same as columns.

Hiding Row 11 through 15

Unhiding Colums 11 thru 15

Hiding and Unhiding Columns in Visual Basic

'Hide Column "A"

Columns("A").Hidden=True

'Unhide Column "A"

Columns("A").Hidden=False


'Hide Columns A through D

Columns("A:D").Hidden=True

'Unhide Columns A through

Columns("A:D").Hidden=False


'referencing entire column of a cell (Both Ex. Hide Column "C")


Range("C1").EntireColumn.Hidden=True

or

Cells(1,3).EntireColumn.Hidden=True


'referencing entire column of a cell (Both Ex. Unhide Column "C")


Range("C1").EntireColumn.Hidden=False

or

Cells(1,3).EntireColumn.Hidden=False


Hiding and Unhidding Rows in Visual Basic

'Hide Row 1

Rows(1).Hidden=True

'Unhide Row 1

Rows(1).Hidden=False


'Hide Rows 1 through 5

Rows("1:5").Hidden=True

'Unhide Rows 1 through 5

Rows("1:5").Hidden=False


' referencing entire row of cell (Both Ex. Hide Row 5)


Range("C5").EntireRow.Hidden=True

or

Cells(5,3).EntireRow.Hidden=True


'referencing entire row of a cell (Both Ex. Unhide Row 5)


Range("C5").EntireRow.Hidden=False

or

Cells(5,3).EntireRow.Hidden=False


Comments

    0 of 8192 characters used
    Post Comment

    • Strugglin2XL profile image

      Ejaz Ahmed 3 years ago from Chennai, India

      Great Article to get started with hiding rows and columns in Excel with VBA. I have come up with a framework to hide and unhide rows/columns in excel with minimal coding. This, I accomplish by using boolean markers directly on the sheet.

      I'd like you to check it out and let me know what you think.

      http://strugglingtoexcel.wordpress.com/2014/04/25/...

    • FordeAhern profile image

      FordeAhern 5 years ago from Broadford, Co. Limerick. ireland

      this is A very useful hub. Thank you. Will bookmark it. Voted up also