Wednesday, May 11, 2016

Excel VBA Macro Delete all images / drawing objects from sheet in one go

Excel VBA Macro code to Delete all images / drawing objects from sheet in one go:

Sub DelObjects()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
          On Error Resume Next
Next sh
MsgBox "Done!"
End Sub


Sunday, May 1, 2016

Excel formula to find overlapping dates in columns

Query: I have a range of dates, start date and end date in a single row in below format.

I want to check for overlap for in each row using formula .


An overlap exists if any of end date is equal to or lesser than start date for other blocks or any of  start date is equal to or greater than end date for other blocks.

We will compare each end date for criteria 1 & compare each start date criteria 2 as mentioned above using following formula.

=IF(SUMPRODUCT((INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,)<=TRANSPOSE(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)))*(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)>=TRANSPOSE(INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,))))>1,"Overlap","No Overlap")

Download sample file.

Post your feedback below & write your query to www.ExcelVbaLab.Com