If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only this time, we change the row into column.
Function LastColumn() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = ix
End Function
The function above will return the appropriate position of the column even when the last column containing data in a hidden state.
The results of this function is the column number, not the column name (1 for A, 2 for B, etc). If what we need is the column name, then we can use the excel column number to column name converter that I wrote before.
Ever want to paste some data into new empty cell after the last cell with data in certain column? Then take a look at this simple macro.
'first, select cell in the first row of that column, like A1, K1, etc
Range("A1").Select
'move to the last cell with data
Selection.End(xlDown).Select
'move to one row below it
ActiveCell.Offset(1, 0).Select
'paste the copied data in there
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Simple isn’t it? Simple code, but will help us a lot each time we wanna move the active cell into one cell below the last cell contained any kind of data, in the same column.
One requirement needed to use this simple excel vba macro, first we need to select the first cell in the same column where we wanna do the selection.
Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide.
Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting Hide, they simply need to choose UnHide, and all the Worksheets in hiding will be revealed.
The above method only working in Microsoft Excel before 2007, in Excel 2007, the menu to hide and unhide columns/cells/worksheets are hidden by default, but we can add this menu into Excel Quick Access Toolbar (tiny menu at top left corner of the window), by accessing menu Customize Quick Access Toolbar (tiny down arrow on the right), select More Commands… » Choose commands from Home Tab » and select menu Hide & Unhide.
The other method on how to hide our Excel Worksheet, more secured, and not really well known by people is using Worksheet xlSheetVeryHidden properties.
To perform this method, first we need to go to Microsoft Visual Basic Editor (ALT+F11), in the project explorer (if the explorer is not showing, click CTRL+R), select Worksheet that we want to hide, then go to Properties Windows (F4), and in the Visible properties, select 2 – xlSheetVeryHidden like in this picture:
If we follow all the guide above, the Worksheet will be disappear/hidden from view, even when we use menu Format > Sheet > UnHide, the Worksheet will not be displayed in the list of Worksheets in hiding.
We can also get the same result using Excel VBA macro, like this:
Private Sub Workbook_Open()
Worksheets("Sheet1").Visible = xlSheetVeryHidden
End Sub
With a simple one line of code, Sheet1 will automatically set to VeryHidden each time the Excel Workbook was opened. Change “Sheet1″ with your Worksheet name.
FIN
