Blog

Delete all pictures in a selected range of cells Excel VBA

Delete all pictures in a selected range of cells Excel VBA The following program Deletes all pictures in a selected range of cells Excel VBA. Sub DeletePics() Dim PicRng As Range Dim Pic As Picture Dim Rng As Range Application.ScreenUpdating = False Set Rng = Range("B1:B100") For Each Pic In ActiveSheet.Pictures Set PicRng =...

Create new CSV file with copied data using Excel VBA

Create new CSV file with copied data using Excel VBA Create New CSV file with copied data  using Excel VBA from a macro file. Besides that,  the data transfer takes place to csv with copy paste program codes. Therefore, the following program generates csv file with copied data. Sub csv_gen() Dim wb_main As Workbook...

Lookup and return multiple matches in Excel

Lookup and return multiple matches in Excel Formula to return multiple matches. It is an array formula. Press Shift+Ctrl+Enter in the formula bar. =IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="Excel",ROW($A$1:$A$10)),ROW(A1)),2),"") =IFERROR(INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$A$2:$A$6="A",ROW(Sheet1!$A$2:$A$6)),ROW(1:1))-1),"") =SUMPRODUCT((--MID($A$2:$A$4,FIND("#",$A$2:$A$4)+1,10))) It uses Index, Small and Row functions to perform the required. Excel Factor 17 Lookup and Return Multiple Matches    

OR logic in Sumproduct function in Excel

OR logic in Sumproduct function in Excel Sumproduct usually will be applied with AND logic. OR logic in sumproduct function can be implemented as detailed below. A) AND condition example is as follows: =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A")) =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B")) B) There are ways to add OR condition using “+” symbol. The “plus sign” (+) is for OR in...

Display Times as Hours/Minutes in Excel

Display Times as Hours/Minutes in Excel Usually, Excel stores time as decimal values and displaying times as hours or minutes. Ex: A1=200 B1=A1/1440 (1440 = 24*60) = 0.1389 Now, use custom format as h:mm (or) h” hours and “m” minutes” There is an another option in Excel using Convert function.  =Convert(A1,”mn”,”hr”) -Ref: https://www.accountingweb.com/technology/excel/display-times-as-hoursminutes-in-excel

Minimum if multiple criteria using Excel

Minimum if multiple criteria using Excel Get the minimum value by checking multiple criteria from different columns. Minimum if multiple criteria is calculated with the following array formulas including nested if conditions. A) The following formula checks different criteria and get the minimum values from value column. =MIN(IF(rng1=criteria1,IF(rng2=criteria2,values))) Press Ctrl+Shift+Enter to convert to array...