Pinoy Tutorials, Tips and Tricks

Commonly Used VBA Statements for Excel VBA Programming

The following table shows a list of commonly used VBA statements that might be useful when creating macros for your Excel projects. To know more information about each statement, consult Excel’s Help system by pressing "F1". <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgcU5Jj1uAwQCyKRQR_VIBvHhP81iLJW8UoLzrNim7XMmJkgKQn_NkWlydLUsEBfkcSmYAEAe_iJF7qb8pqsjbz8C4ZXzRoScOOmFGH6du2zb1-CY4bWjfh036NqYF93YD6Pc51m4laZoR/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="560" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgcU5Jj1uAwQCyKRQR_VIBvHhP81iLJW8UoLzrNim7XMmJkgKQn_NkWlydLUsEBfkcSmYAEAe_iJF7qb8pqsjbz8C4ZXzRoScOOmFGH6du2zb1-CY4bWjfh036NqYF93YD6Pc51m4laZoR/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_1.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidgc0laEJwk7sh0tJTVEyUx57Lveg0gQPXSsC9P0jCNcaj1SgMLRedv2urQA_peAXPJErUftJdIoDwKLHrzDqieIc1LRSZJo67GKqF9zHe49l0-DDP4MARxtZWIEWD_LZe149NTbEd6QBA/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidgc0laEJwk7sh0tJTVEyUx57Lveg0gQPXSsC9P0jCNcaj1SgMLRedv2urQA_peAXPJErUftJdIoDwKLHrzDqieIc1LRSZJo67GKqF9zHe49l0-DDP4MARxtZWIEWD_LZe149NTbEd6QBA/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_2.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivSsKInS9ce2dAl4zh_g0parfHvPpgvKr7iMnxtTu-dNmM34NY8lydOFQ2lSG6CK0Rj6-8iNlkGXaZDfbbO_TSqnEG3VsKlYmZ6ywhJjfeeIB1EpSzNM-epCtsd80eU0Ya7sIr8USTS86V/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivSsKInS9ce2dAl4zh_g0parfHvPpgvKr7iMnxtTu-dNmM34NY8lydOFQ2lSG6CK0Rj6-8iNlkGXaZDfbbO_TSqnEG3VsKlYmZ6ywhJjfeeIB1EpSzNM-epCtsd80eU0Ya7sIr8USTS86V/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_3.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNThxGTWvWN_SvGTKZl684rXEdrBfrFQu-UlipSdTBxF6Sm1r1R6YnfWdEi5adq4jhyPcZp3oU4Pcc6kGA-vdw32StpeEAhCBDP1PGEWvQMuqwvAz2jEkZuaRwyfKEIWv9MO-1lQXiOPr6/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="539" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNThxGTWvWN_SvGTKZl684rXEdrBfrFQu-UlipSdTBxF6Sm1r1R6YnfWdEi5adq4jhyPcZp3oU4Pcc6kGA-vdw32StpeEAhCBDP1PGEWvQMuqwvAz2jEkZuaRwyfKEIWv9MO-1lQXiOPr6/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_4.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg3ckyL7AKqyMoljJbxR-WlDtT346iMX8Msx7EDpmpEvB_OBnTNnYEmhL5newulUkW2mkRLEDPUbJ-STcMEwNFntvvrAhcNS_OnDMY1rv7tsa5Q8VUNTfVkniDMbRLbcIN6g4PFzSDXMwj/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="409" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjg3ckyL7AKqyMoljJbxR-WlDtT346iMX8Msx7EDpmpEvB_OBnTNnYEmhL5newulUkW2mkRLEDPUbJ-STcMEwNFntvvrAhcNS_OnDMY1rv7tsa5Q8VUNTfVkniDMbRLbcIN6g4PFzSDXMwj/s1600/Common+VBA+Statements+for+Excel+VBA+Programming_5.png" width="640" /></a></div>
<br />
<br />
<br />

excel microsoft excel VBA Commonly Used VBA Statements for Excel VBA Programming

The following table shows a list of commonly used VBA statements that might be useful when creating macros for your Excel projects. To know ...

Remove and Replace All Characters in Microsoft Excel using VBA

11:30 PM OmelSoft 0

excel microsoft excel VBA Remove and Replace All Characters in Microsoft Excel using VBA

The following FindandReplace() function allows you to replace or remove specific text and characters you want in Microsoft Excel. Just copy...

How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

1:59 AM OmelSoft 0

<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" width="200" /></a></div>
The following function below allows you to get the number of visible rows from a filtered sets of rows in Excel using VBA. The function takes two arguments which is the Column and StartRow. Calling the FilterCount() function returns the number of visible rows. Also added an error handler which process the error description to determine if there's a visible row.<br />
<br />
<b>Parameters:</b><br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><b><i>Column</i></b>:<span class="Apple-tab-span" style="white-space: pre;"> </span>The column of the data to be filtered. If you have multiple columns being filtered you can just set the first Column or any column in the dataset.  <span class="Apple-tab-span" style="white-space: pre;"> </span><br />
        <b><i>StartRow</i></b>:<span class="Apple-tab-span" style="white-space: pre;"> </span>Start row of the data to be filtered.  <br />
<br />
<pre class="brush: vb">Function FilterCount(ByVal Column As String, ByVal StartRow As Long) As Long

On Error GoTo errHandler
  
    FilterCount = Application.WorksheetFunction.CountA(ActiveSheet.Range(Column & StartRow, Cells(ActiveSheet.UsedRange.Rows.Count, Range(Column & StartRow).Column)).SpecialCells(xlCellTypeVisible))

'Debug.Print FilterCount
  
Exit Function
errHandler:
    If Err.DESCRIPTION = "No cells were found." Then
        'Set count to 0
        FilterCount = 0
    End If
  
    'Debug.Print FilterCount
  
End Function</pre>
<br />
<b>Usage: </b><br />
<br />
<pre class="brush: vb">Sub Count()
    Debug.Print FilterCount "A", 20
End Sub</pre>
<br />
<b>Where: </b><span class="Apple-tab-span" style="white-space: pre;"> </span><br />
          "A" - is the column or first column in the column lists. <span class="Apple-tab-span" style="white-space: pre;"> </span><br />
           20  - is the start row of the data set.  <br />
<div>
</div>
</div>

excel microsoft excel VBA How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

The following function below allows you to get the number of visible rows from a filtered sets of rows in Excel using VBA. The function t...

How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

12:28 AM OmelSoft 0

<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpzopGHiGhnkZBMXoFPaDUbWgzkh_lcgu1SffQK_WxhkN4eCFpWSO1In_MTA2ndChgxdeb6zGHMNeJzj1uH9a2THPoxGWrod-rwFdLNehpogNY_O0LybDkW-xKqHXnLT-jqBHd3iuBkQBS/s1600/Copy+Filtered+Rows+in+Excel+Using+VBA_1.jpg" width="320" /></a></div>
<div style="text-align: justify;">
You might be working on a project where you need to filter sets of data and create a raw data of that filtered sets of data to a new sheet or range.<br />
<br />
By default, Excel copies hidden or filtered cells in addition to visible cells. If some cells, rows, or columns on your worksheet are not displayed, you have the option of copying all cells or only the visible cells. </div>
<br />
The following snippet allows you to automate the process in microseconds.<br />
<br />
<b><span style="color: #cc0000; font-size: large;">[</span><span style="background-color: #cc0000;"><span style="color: #f4cccc;">VBA</span></span><span style="color: #cc0000; font-size: large;">] </span></b><br />
<pre class="brush: vb">Public Function GetFilteredData()
    Dim rawWs As Worksheet            'RAW DATA WORKSHEET
    Dim tarWs As Worksheet            'TARGET WORKSHEET
    
    'Replace this with your actual Worksheets
    Set rawWs = Sheets("Raw Data")    
    Set tarWs = Sheets("Filtered Data Visualizations")
    
    Application.ScreenUpdating = False

'Clear old contents of the Target Worksheet
    tarWs.Range("A2:N" & Rows.Count).ClearContents
    
    '****************************************************
    ' Select Raw Data Sheet and
    ' Copy only the visible rows if filter is applied
    '
    rawWs.Select
    Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, Range("N2").Column)).SpecialCells(xlCellTypeVisible).Copy
    
    '****************************************************
    'Select the Target worksheet and
    'Paste the copied data
    '
    tarWs.Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Range("A2").Select
    Application.ScreenUpdating = True
End Function</pre>
<br />
<div style="text-align: center;">
<b>Output on a new sheet shown below.</b></div>
<div style="text-align: center;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjw8Vl3RpEwKitCYUgo2rnuZOolHyb2vxqOHBjyS8SaT8qopTAtWN7e_pG0hKT3EnqNgAGn6S06TtlW5nUzIfLbVnwwOB42U-QuW0GJoWarzR1J696BBbnLAPX6qT4sbWOt0bSmzW-G48C3/s1600/Copy+Filtered+Rows+in+Excel+using+VBA.jpg" width="320" /></a></div>
<br />

excel microsoft excel VBA How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

You might be working on a project where you need to filter sets of data and create a raw data of that filtered sets of data to a new shee...

How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

12:47 AM OmelSoft 0

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.<br /><br /><span style="color: #cc0000; font-size: large;"><b>[</b></span><span style="background-color: #cc0000;"><span style="color: #f4cccc;"><b>VBA</b></span></span><b><span style="color: #cc0000; font-size: large;">]</span></b><br /><pre class="brush: vb">Dim FilteredRows as Variant<br /><br />Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean)<br /><br />    Dim Rng As Range, rngF As Range, rngVal As Range      'Ranges<br />    Dim val As Variant       'Range Value<br />    Dim i As Integer         'Counter<br />    Dim lRow as long         'Last Row<br /><br />    Application.ScreenUpdating = False<br /><br />    Sheets("Raw Data").Select<br />    lRow = WorksheetFunction.CountA(Range("A:A"))<br /><br />    'Set the range of all visible cells of the filtered data<br />    Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _<br />    Range("A2").Column)).SpecialCells(xlCellTypeVisible)<br />    <br />    For Each Rng In Range("$A2:$A$" & lRow)<br />        If Not Intersect(Rng, rngF) Is Nothing Then<br />            If rngVal Is Nothing Then<br />                Set rngVal = Rng<br />            Else<br />                Set rngVal = Union(rngVal, Rng)<br />            End If<br />            If rngVal.Cells.Count = lRow Then Exit For<br />        End If<br />    Next Rng<br />    <br />    'Resize array variable<br />    ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant<br />    <br />    For Each val In rngVal<br />        If RowPrefixed = True Then<br />            FilteredRows(i) = val.Address<br />        Else<br />            FilteredRows(i) = Split(val.Address, "$")(2)<br />        End If<br /><br />        Debug.Print val.Address & " - " & Split(val.Address, "$")(2)<br />        i = i + 1<br />    Next val<br />    <br />    Debug.Print rngVal.Address<br />    <br />    Applicaiton.ScreenUpdating = True<br />End Function<br /></pre><br />To use the above function, you can assigned the following macro to a button or shape. <br /><pre class="brush: vb">Sub SetFilter()<br />    Call GetFilteredRows(True)<br />End Sub<br /></pre><br />And you can see the output in the Immediate window as shown below.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizGItpJgTKQprIBxhEpp9iQagfqwq4LecPAgn8zu_ql7LbZ3MNJZhmlnzS7m6YR6bVYKRa5S3nKisxU1x1H_R-raeF0TuUSGMwF7SMYbS86d1v3FypGp2amxZobHns0oRc66kOvIx0LH6s/s1600/Get+Row+Address+of+Filtered+Data+in+Excel+using+VBA.jpg" width="400" /></a></div><br />

excel microsoft excel VBA How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA. [ VBA ] Dim Filte...

Pinoy Tutorials, Tips and Tricks

Commonly Used VBA Statements for Excel VBA Programming

Remove and Replace All Characters in Microsoft Excel using VBA

How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

How to Copy Only the Visible Rows of a Filtered Data in Excel using VBA

How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA

Popular Posts

TAG CLOUD

Blog Archive