Row & Column

This page is focused on manipulation around rows and columns in an Excel file with VBA macro.

On a spreadsheet, the rows and columns are the key elements on which you will manipulate your data. These objects have many properties and methods and are considered as range object.

Layout Rows & Columns

Thinking of the design aspect of your spreadsheet is give the best readability possible.

Know how to read the most important data thanks to a clear visual display.

In the code, you can automatically format your results by working the layout (aligning the text horizontally and vertically, bold, italic, text size, font, text colour, cell colour, management of borders …), globally this is the same method and procedure than the range object.

Sub LayoutRows()

    With Rows(2) 'Apply the layout on rows 2
        .RowHeight = 25 'Size of the row
        .Font.Bold = True
        .Font.Italic = False
        .Font.Size = 16 'Size of the text
        .Font.Color = RGB(38, 38, 38) 'Color of the text
        .Interior.Color = RGB(237, 237, 237) 'Color inside all cells
        .WrapText = True 'Display all the text in the cell
        .HorizontalAlignment = xlCenter 'Center the text
        .VerticalAlignment = xlCenter 'Center the text
        'Border horizontal inside the cell 
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlInsideHorizontal).Weight = xlThin 'Line thickness
        'Border vertical inside the cell 
        .Borders(xlInsideVertical).LineStyle = xlContinuous 'Style of the line
        .Borders(xlInsideVertical).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlInsideVertical).Weight = xlThin 'Line thickness
        'Border Right inside the cell         
        .Borders(xlEdgeRight).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeRight).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeRight).Weight = xlThin 'Line thickness
        'Border Left inside the cell         
        .Borders(xlEdgeLeft).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeLeft).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeLeft).Weight = xlThin 'Line thickness
        'Border Top inside the cell 
        .Borders(xlEdgeTop).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeTop).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeTop).Weight = xlThin 'Line thickness
        'Border Bottom inside the cell 
        .Borders(xlEdgeBottom).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeBottom).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeBottom).Weight = xlThin 'Line thickness
    End With

End Sub

Replacing the rows by the column, we can apply the same procedure to do a layout. Only the property Size will be different with ColumnWidth.

Sub LayoutColumns()
    
    With Columns("A:D") 'Apply the layout from column A to D
        .ColumnWidth = 25 'Size of the column
        .Font.Bold = True
        .Font.Italic = False
        .Font.Size = 16 'Size of the text
        .Font.Color = RGB(38, 38, 38) 'Color of the text
        .Interior.Color = RGB(237, 237, 237) 'Color inside all cells
        .WrapText = True 'Display all the text in the cell
        .HorizontalAlignment = xlCenter 'Center the text
        .VerticalAlignment = xlCenter 'Center the text
        'Border horizontal inside the cell 
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlInsideHorizontal).Weight = xlThin 'Line thickness
        'Border vertical inside the cell 
        .Borders(xlInsideVertical).LineStyle = xlContinuous 'Style of the line
        .Borders(xlInsideVertical).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlInsideVertical).Weight = xlThin 'Line thickness
        'Border Right inside the cell         
        .Borders(xlEdgeRight).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeRight).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeRight).Weight = xlThin 'Line thickness
        'Border Left inside the cell         
        .Borders(xlEdgeLeft).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeLeft).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeLeft).Weight = xlThin 'Line thickness
        'Border Top inside the cell 
        .Borders(xlEdgeTop).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeTop).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeTop).Weight = xlThin 'Line thickness
        'Border Bottom inside the cell 
        .Borders(xlEdgeBottom).LineStyle = xlContinuous 'Style of the line
        .Borders(xlEdgeBottom).Color = RGB(191, 191, 191) 'Color of the border
        .Borders(xlEdgeBottom).Weight = xlThin 'Line thickness
    End With

End Sub

Find the Last Row

In front of a sheet full of data, you will have to manipulate the data, and certainly loop on them. To do it, finding the last row and also the last column will be inevitable.

It exists several methods to find the last row of your data quickly with one line of code.

Below several methods :

Sub FindTheLastRow()

    Dim Sh As Sheet
    Dim LastRow As Long

    Set Sh = ActiveSheet

    'You can use the Find Function
    LastRow = Sh.Cells.Find("*", searchorder:=xlByRows searchdirection:=xlPrevious).Row

    'Using SpecialCells Function 
    '(be careful, in a cell with a format without text 
    'it can be the last cell detected, not always as expected)
    LastRow = Sh.Cells.SpecialCells(xlCellTypeLastCell).Row

    'Do the same action than the key shortcut Ctrl + Shift + End
    LastRow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).Row

    'Do the same action than the key shortcut Ctrl + Shift + Down
    LastRow = Sh.Range("A1").CurrentRegion.Rows.Count

    'With UsedRange
    LastRow = Sh.UsedRange.Rows(Sh.UsedRange.Rows.Count).Row

    'Method with a range already defined, here "DataRng"
    LastRow = Sh.Range("DataRng").Rows.Count

End Sub

Find the Last Column

As seen above, we can use several methods with the same principle that to find the last row to find the last column of your spreadsheet.

Sub FindTheLastColumn()

    Dim Sh As Sheet
    Dim LastRow As Long

    Set Sh = ActiveSheet

    'Do the same action than the key shortcut Ctrl + Shift + End
    LastColumn = Sh.Cells(1, Sh.Columns.Count).End(xlToLeft).Column

    'Do the same action than the key shortcut  Ctrl + Shift + Right
    LastColumn = Sh.Range("A1").CurrentRegion.Columns.Count

    'With UsedRange
    LastColumn = Sh.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

    'Method with a range already defined, here "DataRng"
    LastColumn = Sh.Range("DataRng").Columns.Count

End Sub

Get the column or row number using find method in VBA

When you work on a large amount of data, it is practical to automatically detect the column or row of a variable.

The data is often sorted in the databases by title to make your macro dynamic. If the source files of your work evolves, it is preferable to automatically detect the titles of the data fields so your macro will be more robust.

Sub GetColOrRowNumber()
    
    'Use Find method to search "Hello"
    'and return the column number of the cell with "Hello"
    'stock on the variable NumColHello
    NumColHello = rows.Find(What:="Hello", LookAt:=xlWhole, _
                                      MatchCase:=False).Column

    'Use Find method to search "Hello"
    'and return the row number of the cell with "Hello"
    'stock on the variable NumColHello
    NumRowHello = Columns.Find(What:="Hello", LookAt:=xlWhole, _
                                       MatchCase:=False).Row
                                              
    MsgBox "Num Col =  " & NumColHello & " / " & _
        " Num Row = " & NumRowHello
End Sub

Number of column and row for a text in a cell

Another way to retrieve them quickly is to loop on the first row of your file with a loop and stock in memory when you find the title of the data.

To use your variables on every modules and procedures, a good way is to declare your variables as public outside the procedure.

'Declare all variable outside a procedure in a module
'to use the variable where you want in the code

Public NumColHello,NumColDate,NumColYear As Integer

Sub GetColNumberOfData()
    
    'Use a For to stock all columns for data
    For jCol =1 To NbRowsFile 
       If Cells(1, jCol).Value2 = "Hello" Then NumColHello = jCol
       If Cells(1, jCol).Value2 = "Year" Then NumColYear = jCol
       If Cells(1, jCol).Value2 = "Date" Then NumColDate = jCol
    Next jCol

End Sub

Insert or delete a specific row

Often you will be confronted on your spreadsheet to automatically add rows or columns. In the same way, you will also need to delete unnecessary data from your analyzes.

To perform these operations you will use the insert and delete attributes of the object rows and columns.

Sub DeleteRows()
'Delete a row

    Rows("4:4").Delete Shift:=xlUp

End Sub

Sub InsertRows()
'Insert a row

    Rows("5:5").Insert Shift:=xlDown
    
End Sub

Group / Ungroup multiple columns

On a worksheet, you often remember the need to hide and unhide columns quickly to have a closer look at the important data for your analysis.

To not waste your time by manually hiding and unhiding columns, one solution is to create buttons + and – that hide and unhide your columns using VBA.

Sub GroupColumn()
'Group column with a "+" button

   Columns("A:E").Columns.Group
        
End Sub

Sub UnGroupColumn()
'Ungroup column with a "-" button
        
   Columns("A:E").Columns.Ungroup
        
End Sub

Hide multiple rows or columns

We saw above a way to let the user choose to hide or uncheck the columns through the group property. It is also possible to hide and unhide the columns, without creating plus and minus buttons. We will use the Hidden attribute in our code.

Sub ToHide()

    'Hide column & rows
    Columns("C").Hidden = True
    Rows("5:8").EntireRow.Hidden = True

    'Unhide column & rows
    Columns("C").Hidden = False
    Rows("5:8").EntireRow.Hidden = False
    
End Sub

AutoFit Columns

In function of the values in your cells, the auto fit column allows you to automatically put columns at the correct size to see all values. In your VBA code, you can select all the column of your sheet using the properties EntireColumn and do an AutoFit.

Sub AutoFitColumns()

    Cells.EntireColumn.AutoFit

End Sub

AutoFit Rows

As the auto fit for columns, in function of the values in your cells, the auto fit rows allows you to automatically put rows at the correct size in to see all values. In your VBA code, you can select all the rows of your sheet using the properties EntireRow and do an AutoFit.

Sub AutoFitRows()

    Cells.EntireRow.AutoFit

End Sub

Do a Text To Columns in VBA

When extracting a database in a CSV (Comma-Separated Values) format, you will often come across difficulties to use an Excel file.

To make it more readable you need to re-format it and that goes through the use of the TextToColums method.

Sub TextToColumn()
'Change a csv in a right format to read data
    
    'Here it is for a comma separation
    Columns("A:A").TextToColumns Destination:=Range("A1"), _ DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
SemiColon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
                
End Sub

Copy a row

To copy a row, you don’t have to use the Copy method, just the Value2 method (which is faster than the Value method as explained in this article).

Sub CopyRow () 
   
   'Copy one row
   Rows("1").Value2 = Rows("2").Value2

   'Copy several rows
   Rows("1:5").Value2 = Rows("10:15").Value2

End Sub

Loop on column

To loop on column or rows, you have to use a For Each loop on the collection of columns.

Sub LoopColumns()
     
    For Each jCol In Sheets("Bitcoin").Columns
        
        'Condition to do something only on pair columns
        If jCol.Column Mod 2 = 0 then 
             '...your instructions...
        End If 

    Next jCol

End Sub

You can do the same kind of loop for rows.

Sub LoopRows()
     
    For Each iRow In Sheets("Bitcoin").Rows
        
        'Condition to do something only on pair rows
        If iRow.Row Mod 2 = 0 then 
             '...your instructions...
        End If 

    Next iRow

End Sub

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (Address never made public)

Name

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s