Conditional Formatting in Excel for Rows

My main objective is to sum up the numbers in column F only when the conditional formatting in column B returns TRUE. However, I need to first determine whether the conditional formatting is true or not. To accomplish this, you can place the following code in the worksheet’s code module: And in a standard code […]

Performing a VLOOKUP Function with Case Sensitivity

In case our dataset has duplicate values with different case sensitivity, we need to perform a case-sensitive search to obtain a specific type of value. If the value being searched is not a numerical value, one can utilize an array formula to locate the row and then use an INDEX function to retrieve the value. […]

Looping Through Cells in a Range Using Excel VBA

Feedback Solution 1: This is a previous post I made that consists of functions to convert column numbers into letters and also to convert letters into column numbers. How to locate the subsequent column in VBA by using an entered value? For resolving the 1004 error, consider attempting a solution similar to this one: EDIT: […]

How to remove empty rows in Excel

Feedback Question: I find myself in a predicament. Despite the existence of values at B3 and D10, the code deletes rows is only applicable to A3 and A10. My aim is to apply delete rows to every sheet within the workbook. Sub Delete() Dim xlastrow As Integer Dim xrow As Integer xrow = 1 Range(“a65000”).End(xlUp).Select […]

Shifting the value of a Vlookup in Excel

Solution 1: @user3561813 has provided an explanation for a possible solution. By utilizing MATCH, the position of the last entry in ColumnB is determined. Then, by moving up seven rows and using INDEX, the content of that row in ColumnB is retrieved. Solution 2: Another approach involves using a single function where the “return vector” […]

Enabling Macro Settings in Excel VBA

With the exception of a splash screen, all sheets in the workbook are set to be “very hidden” using VBA or the VBA editor. If macros are enabled, these sheets will be unhidden when the workbook is opened and hidden again when it is closed. If macros are disabled, the user will only be able […]

Transform a matrix into a table with three columns using techniques like reverse pivot, unpivot, flatten, and normalize

To update your source table, you can click on the Power Query result table sheet and select “Refresh All” under the Data ribbon. If you want to convert an Excel matrix into a table, you can use the “reverse pivot”, “unpivot”, or “flatten” method. One solution for this is to follow the steps for Excel […]

Power Queries Merged for Exclusive Connection

Feedback Question: In my workbook, there are two queries that are interdependent. While one query is configured to load data after performing merging and expanding operations, the other query is set to establish a connection only. Upon observing, I noticed that the query which is set to “Connection only” does not refresh automatically while Refresh […]

Eliminating Concealed Columns in Excel: A Guide

Feedback Solution: Something like so… Dim x As Excel.Application Dim r As Recordset Dim w As Excel.Worksheet Dim l As Long Dim c As New Collection Set r = Me.Recordset Set x = New Excel.Application x.Visible = 1 If x.Workbooks.Count = 0 Then x.Workbooks.Add Set w = x.Worksheets(1) For l = 0 To r.Fields.Count – […]