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.<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...

Read more »

How to Suppress Warning and Alerts in Excel using VBA

In Microsoft Excel, you can suppress the message warning and alerts especially when you try to create a copy of a worksheet from a workbook containing macros to a new workbook which you wanted to save it as <i>.xls</i> or <i>.xlsx</i> extensions. By that, when you save the document it will prompt you to whether save the work as macro-enabled or macro-free workbook.<br /> <input name="IL_IN_TAG" type="hidden" value="1" /> <br /> <div class="separator" style="clear: both; text-align: center;"> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9ymOKNotdfLRc5hTmhTMHvCw3zmAxFHz4llhRn_3djoC2T-vSTtyevQiw8oyZa3li-e0O36PucZGAk_6gXJTCrtCs8vnvFWsGmPSBE_TvzRnQhzsSVYxxJ5VDozGEHG14qurj5LlSZzLi/s1600/Suppress+Warning+and+Alerts+in+Excel.png" width="320" /></a></div> You can do that by setting <b>DisplayAlerts</b> to <b>False</b> with VBA code.<br /> <br /> <!--INFOLINKS_OFF--> <br /> <pre class="brush: vb">Application.DisplayAlerts = False 'DO SOME TASKS HERE Application.DisplayAlerts = True</pre> <!--INFOLINKS_ON-->

excel microsoft excel VBA How to Suppress Warning and Alerts in Excel using VBA

In Microsoft Excel, you can suppress the message warning and alerts especially when you try to create a copy of a worksheet from a workbook ...

Read more »

How to Create a Custom Checkbox using only the Cells in Microsoft Excel

<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="120" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxkACDwIw1FjBEpYlzaGOSEIvJUxFbK77z-KTMBuoeR6qgDNODMyj5g1s-w-DkklQI1Dj1WGWNAnjmfLk3lCHRRomArj4UfnEld1x0-MtMV53CYRiihRzAtz_dyP-KcgFlUG5hFnrCV7o/s1600/Custom+Checkbox+using+Excel+Cells.gif" width="320" /></a></div><div style="text-align: justify;">You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose their option. </div><br /><div style="text-align: justify;">We'll, if you want to have another look aside from the default check box control. You can still create and customize your check box controls with a little VBA code to get it done.</div><br /><div style="text-align: justify;">In this example, I have here choices for the reason of a leave application form. I make use of the Microsoft Excel's Cell as a check box. It's just a matter of resizing the rows and columns to make the size of the cell equal. </div><br /><b>1.)</b> Now, let's create first the list of choices, as you can see below I've resized the cells and add borders on the cells which we'll set as a check-cell box control.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJzKV7SKFbDsQA2abXmIunZpb3fBZ1UArc_NRlmQpIyItELxBysCXJYEQFSkr-DWuzjmp3DGFEDURzUWpr8_G3nLKK7erbOTjOluKaesDftX_K2fSuVvAx8UFkbe4_IrvbvowbzSHyq9Q/s1600/Check-Cell+Box+Controls.jpg" width="320" /></a></div><br /><b>2.)</b> Right Click on the Worksheet, then Click "<b><i>View Code</i></b>".<br /><b>3.)</b> Then Copy and Paste the following codes below into the code editor window.<br /><br />The following code will set the Target Range of when the Check-Cell box will be triggered. I set the columns to <b>G</b> &<b> H</b> or it's numeric equivalent <b>7</b> & <b>8</b>. Then create case statement of what rows it will run. The columns and rows may vary depending on your actual template. So you can set it to match your template.<br /><pre class="brush: vb">Private Sub Worksheet_SelectionChange(ByVal Target As Range)<br />    With Target       <br />        'SET THE TARGET COLUMN TO COLUMNS 7 AND 8<br />        If Target.Column >= 7 And Target.Column <= 8 Then<br />            'SELECT CASE OF WHAT ROW WILL BE SELECTED<br />            Select Case Target.Row<br />                Case 19 'SICK LEAVE<br />                    CheckSelected 19, 7<br />                Case 21 'VACATION LEAVE<br />                    CheckSelected 21, 7<br />                Case 23 'PERSONAL<br />                    CheckSelected 23, 7<br />                Case 25 'EMERGENCY<br />                    CheckSelected 25, 7<br />                Case 27 'APPOINTMENT<br />                    CheckSelected 27, 7<br />                Case 29 'OTHERS<br />                    CheckSelected 29, 7<br />                Case Default<br />            End Select<br />        End If<br />    End With<br />End Sub<br /><br /><br /><br />'CHECK IF THE REASON IS CURRENTLY SELECTED<br />Sub CheckSelected(Row As Integer, Col As Integer)<br />    With Cells(Row, Col)<br />        If .Value = "+" Then<br />            .Value = ""<br />            ResetSelectedColor Row, Col<br />        Else<br />            .Value = "+"<br />            ApplySelectedColor Row, Col<br />        End If<br />    End With<br />End Sub<br /><br /><br /><br />'CHANGE THE BACKGROUND OF THE SELECTION<br />Sub ApplySelectedColor(Row As Integer, Col As Integer)<br />    With Cells(Row, Col).Interior<br />        .Pattern = xlSolid<br />        .PatternColorIndex = xlAutomatic<br />        .ThemeColor = xlThemeColorAccent1<br />        .TintAndShade = 0.599993896298105<br />        .PatternTintAndShade = 0<br />    End With<br />End Sub<br /><br /><br />'REMOVE THE BACKGROUND OF THE SELECTION<br />Sub ResetSelectedColor(Row As Integer, Col As Integer)<br />    With Cells(Row, Col).Interior<br />        .Pattern = xlNone<br />        .TintAndShade = 0<br />        .PatternTintAndShade = 0<br />    End With<br />End Sub<br /><br /></pre><br /><b>4.) </b>Now, if you select the Cells it should change the background with the "+" value. You may also change the value to whatever you want.<br /><div><br /></div>

excel microsoft excel How to Create a Custom Checkbox using only the Cells in Microsoft Excel

You might be creating templates with Microsoft Excel, and one of the feature you want is add a check boxes control where users can choose th...

Read more »

Using Named Ranges in Microsoft Excel

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range of cells. So that you can refer to them in formulas using that name.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh06uGkSJiDjEkLowyCcjAHY6jQY4WXTWrTaGY8kgpxuc81T9jgywX-7QngTbGR0hSC-o5YclSKKEaUCH02Cz_-QH0-KHzOI79pnusRVXHVaOhQYwbUZXLHBzW-FCzHXR0yFZ5FQCv3aFJh/s1600/Named+Range+Example.png" width="320" /></a></div><br /><br />You can view the complete tutorial here <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">How to Use Named Ranges in Microsoft Excel </a>.<br /><br />When using Named Ranges there are also set of rules which you need to know like the scope where you can use the specified named range. If it's within only a single worksheet or the entire workbook.<br /><br />Check this out <a href="http://excel-name-ranges.blogspot.com/2014/03/excel-2007-named-range-rules.html" target="_blank">Named Range Rules</a> and be totally aware of the do's and don'ts when using this functions.<br /><br /><b><span style="color: #cc0000; font-size: large;">Managing Your Named Ranges</span></b><br /><b><br /></b>There'll come a time when you want to edit or delete a Named Range. To do this access the Name Manager on the Formulas tab of the ribbon. The Name Manager Dialog box will open.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdL-t-vDb3OoGSP28mbJX53I8dEhYb_qfK0c9KBU-VRNDRwzH5tnFQ6cJfO3Cnv8jz7roTMB_XXGTc3nUNNg7qIWjdLfVjHkBMeAg90WUQvE1PDOfy_A0upqP6D6vAF5ID6dMa_fRQqRPs/s1600/Name+Manager.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi42HKWte4RD6Fk9GxpdIRGgBpY3V13Vyq-uNQkow1r21I7nZE5t4df-Xox7s9ojRgXuty8meAH1ixf1oYSlrCxo-Nhd8f8ljRCs3WPoynYoIEbdKSWlo6Eqx8D7MyKs-GqqrNFLkWMHAGw/s1600/Name+Manager+Window.png" width="320" /></a></div><br />From here you can Edit and Delete your Named Ranges, or even create new ones. Remember, once you delete a name you cannot undo that action.<br /><br /><br /><b><span style="color: #cc0000; font-size: large;">Different uses for Named Ranges in Excel:</span></b><br /><b><span style="font-size: large;"><br /></span></b>1) <b>Formula References:</b> Simplify the creation and retrospective interpretation of formulas by using Named Ranges in your formulas<br /><br />2) <b>Multiple Print Areas</b> on one worksheet; Selecting non-contiguous print areas is quick and easy using Named Ranges. Note: Excel 2007 onward allows you to set multiple print areas on the Page Layout Tab of the Ribbon under Print Area.<br /><br />3) <b>Reduce worksheet clutter</b> with Named Constants. Sounds complicated but it's not. A Named Constant is just a fancy name given to values you might use repeatedly in your formulas. For example, let's say you're planning to have a year-end inventory sale and wanted to give a 10% discount on selected products, you could give the value "10%" a name like "Discount" and then you could:<br /><br /><ul><li>Write your formulas like this =C1-(C1*Discount). This is more intuitive when you come to review the spreadsheet later, or for anyone else reviewing it.</li><li>Update the value globally by editing it once in the Name Manager. This will then automatically update the formulas.</li><li>Reduce clutter on your spreadsheet. Often a helper cell is set up with this key figure in to achieve these benefits, but if you've got many of these key figures your spreadsheets can become crowded.</li></ul><br />To create a Named Constant See--<a href="http://excel-name-ranges.blogspot.com/2014/03/how-to-setup-named-constant-in-excel.html" target="_blank">How to Setup a Named Constant in Microsoft Excel</a>.<br /><br />

excel microsoft excel Using Named Ranges in Microsoft Excel

Named Ranges in Excel enable you to give one, or a group of cells a name other than the default B4 for a single cell, or B2:E20 for a range ...

Read more »

Scrape Website Data into Excel using VBA

<div dir="ltr" style="text-align: left;" trbidi="on">I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(dot)renewableuk(dot)com. Please also read the privacy policy of the website before mining data. <br /><input name="IL_IN_TAG" type="hidden" value="1" /> <br /><div style="text-align: justify;"><div class="separator" style="clear: both; text-align: center;"></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="129" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH8X5SccGIQiU27YplHnzm9-EfKZak6GAWqFqbKc3CWECEcHElhzpWwGEu78YR45e0iJQDGbqh7AlX7A7MQ3uNk4e3-UCBfQ_FzHj4Ff9oID4Sq4sgiaqVQZYgNdkp4dwkFdTWtcf-Pxo9/s1600/order+list.png" width="320" /></a><br /><b>Goal:</b><br />Get all data under all column headings which can be found on this <a href="http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm" rel="nofollow" target="_blank">website</a> i.e.<br />Wind Project, Region, ..., Type of Project <br /><br /><b>Requirements:</b><br /><div style="text-align: justify;">You need to add a reference, Microsoft HTML Object Library on your VBA project.</div><br /><b>Usage:</b><br /><div style="text-align: justify;">You can call the <i><b>ProcessWeb()</b></i> sub directly by pressing F5 on the Microsoft Visual Basic Window.</div><div style="text-align: justify;">Or you can add a button on your excel worksheet then assign <b>ProcessWeb()</b> as the macro.</div><br /><b>VBA CODE:</b><br /><!--INFOLINKS_OFF--> <br /><pre class="brush: vb">Function ScrapeWebPage(ByVal URL As String)<br /> Dim HTMLDoc As New HTMLDocument<br /> Dim tmpDoc As New HTMLDocument<br /> <br /> Dim i As Integer, row As Integer<br /> Dim WS As Worksheet<br /> Set WS = Sheets("DATA")<br /> <br /> 'create new XMLHTTP Object<br /> Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")<br /> XMLHttpRequest.Open "GET", URL, False<br /> XMLHttpRequest.send<br /> <br /> While XMLHttpRequest.readyState <> 4<br /> DoEvents<br /> Wend<br /><br /> With HTMLDoc.body<br /> 'Set HTML Document<br /> .innerHTML = XMLHttpRequest.responseText<br /> <br /> 'Get only Order List Tag of HTML Document<br /> Set orderedlists = .getElementsByTagName("ol")<br /> <br /> 'Reset the Document to the HTML of the second ordered list element<br /> 'where we only need to extract the data<br /> .innerHTML = orderedlists(1).innerHTML<br /> <br /> 'Now, we'll get the list items<br /> Set ListItems = .getElementsByTagName("li")<br /> <br /> 'Declare data variable for p values<br /> Dim iData As Integer<br /> row = lastRow + 1<br /> <br /> 'Let's process each data of the list items<br /> For Each li In ListItems<br /> 'Start's at 1st column<br /> iData = 1<br /> With tmpDoc.body<br /> 'Set the temp doc<br /> .innerHTML = li.innerHTML<br /> <br /> 'There are about 10 columns, so there are 10 p's<br /> Set ps = .getElementsByTagName("p")<br /> <br /> For Each p In ps<br /> 'Put the value of p to each cells define below<br /> WS.Cells(row, iData).Value = p.innerText<br /> <br /> 'increment it by 1 which starts at column 1<br /> iData = iData + 1<br /> Next<br /> End With<br /> row = row + 1<br /> Next <br /> End With<br />End Function<br /><br />'Get the total number pages we need to scrape<br />Function totalPage() As Integer<br /> Dim HTMLDoc As New HTMLDocument<br /> Dim tmpDoc As New HTMLDocument<br /> Dim html As String<br /> Dim mask As String<br /> Dim URL As String<br /> <br /> URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm"<br /> <br /> Set XMLHttpRequest = CreateObject("MSXML2.XMLHTTP")<br /> XMLHttpRequest.Open "GET", URL, False<br /> XMLHttpRequest.send<br /> <br /> html = XMLHttpRequest.responseText<br /> <br /> With HTMLDoc.body<br /> .innerHTML = Mid(html, InStr(1, html, "<span chr="" class=" & Chr(34) & " page-dotted="">"), 300)<br /> mask = Mid(.innerHTML, InStr(1, LCase(.innerHTML), "</span>") - 2, 2)<br /> End With<br /> <br /> totalPage = mask<br /> <br />End Function<br /><br />Function lastRow() As Long<br /> lastRow = Range("A65536").End(xlUp).row<br />End Function<br /><br />Sub ProcessWeb()<br /> Dim URL As String<br /> Dim i As Integer<br /> <br /> Range("2:2", Selection.End(xlDown)).ClearContents<br /> Range("A2").Select<br /> <br /> Application.ScreenUpdating = False<br /> Application.Cursor = xlWait<br /> <br /> URL = "http://www.renewableuk.com/en/renewable-energy/wind-energy/uk-wind-energy-database/index.cfm/page/"<br /> <br /> For i = 1 To totalPage<br /> ScrapeWebPage URL & i<br /> Application.StatusBar = "Please wait while processing page " & i & " of " & totalPage & "..."<br /> Next i<br /> <br /> Application.ScreenUpdating = True<br /> Application.Cursor = xlDefault<br /> Application.StatusBar = ""<br /> <br /> MsgBox "Data Extraction is Done!"<br /> <br />End Sub<br /></pre></div><!--INFOLINKS_ON--> </div>

excel hot microsoft excel Scrape Website Data into Excel using VBA

I'll be showing you an example on how to Scrape Data from a Website into Excel Worksheet using VBA. We'll be scraping data from www(...

Read more »