how to transform a table in Excel from vertical to horizontal but with different length

Here, a solution that uses DROP/REDUCE/VSTACK pattern to generate each row. Check for example @JvdV’s answer from this question: How to split texts from dynamic range? and a similar idea DROP/REDUCE/HSTACK pattern to generate the columns for a given row. In cell E2 put the following formula: =LET(set, A2:B13, IDs, INDEX(set,,1), dates, INDEX(set,,2), HREDUCE, LAMBDA(id, … Read more

Are ActiveX Controls Disabled?

From other forums, I have learned that it is due to the MS Update and that a good fix is to simply delete the file MSForms.exd from any Temp subfolder in the user’s profile. For instance: C:\Users\[]\AppData\Local\Temp\Excel8.0\MSForms.exd C:\Users\[]\AppData\Local\Temp\VBE\MSForms.exd C:\Users\[]\AppData\Local\Temp\Word8.0\MSForms.exd Of course the application (Excel, Word…) must be closed in order to delete this file.

How to change Format of a Cell to Text using VBA

To answer your direct question, it is: Range(“A1”).NumberFormat = “@” Or Cells(1,1).NumberFormat = “@” However, I suggest changing the format to what you actually want displayed. This allows you to retain the data type in the cell and easily use cell formulas to manipulate the data.

CountIf With Filtered Data

I was able to determine that: SUBTOTAL(3,OFFSET(B2:B18,ROW(B2:B18)-MIN(ROW(B2:B18)),,1)) is used to return an array of which cells are visible and hidden in the range. 1 is returned for visible and 0 is returned for hidden. ISNUMBER(SEARCH(“Pear”,B2:B18))+0) is used to return an array of which cells contain “Pear”. If “Pear” is found, 1 is returned, else 0. … Read more

Excel macro – paste only non empty cells from one sheet to another

Looks like you may be making some common rookie mistakes here (it’s okay we all did it). VBA example with line-by-line explanations TIP: Try not to use “Select” or “Copy”. Why use select when all you have to do is reference the cells themselves? For example, instead of using Sheets(“codes”).Select Range(“A5:A100”).Select Selection.Copy Sheets(“Sheet2”).Select Range(“B28”).Select ActiveSheet.Paste … Read more

Create a new object using the text name of the class

CallByName function can help you. Let’s say there are some class modules in your project: clsSample0, clsSample1 and clsSample2. Add a new class module named clsSpawner, which lists all target classes as public variables having the same names, and declared with New keyword: Public clsSample0 As New clsSample0 Public clsSample1 As New clsSample1 Public clsSample2 … Read more

Change font color for a part of text in cell

You can use Characters cell’s property like : Cells(1,1).Characters(Start:=2, Length:=3).Font.Color = RGB(255, 0, 0) This should be a good start : Sub vignesh() Dim StartChar As Integer, _ LenColor As Integer For i = 1 To 5 With Sheets(“Sheet1”).Cells(i, 1) StartChar = InStr(1, .Value, “|”) If StartChar <> 0 Then LenColor = Len(.Value) – StartChar … Read more