Andrew Bishara agbishara

agbishara / VBA - Filter Data

Created April 15, 2014 15:51

This Filters a range of data by a string, its setup for a specific usage

	Public Function fitlerBySVP(Name As String, ws As Worksheet)
	ws.Range("$A$1:$F$38409").AutoFilter Field:=4, Criteria1:= _
	Name
	End Function

agbishara / VBA - Current Time as String

Created April 15, 2014 15:52

Current Time as formatted String

Format$(Date, "mm-dd-yyyy")

agbishara / VBA - Alert

Created April 15, 2014 15:54

Show a Popup in Excel using VBA

MsgBox "Finished! :)"

agbishara / VBA - Generate Unique Dictionary of Strings from Range

Created April 15, 2014 15:55

This takes something like a list of names and returns a dictionary of unique ones you can loop through

	uniqueValues(Range("sheet!D:D"))

	Function uniqueValues(InputRange As Range)
	Dim dict As New Scripting.Dictionary
	Dim cell As Range
	For Each cell In InputRange
	If cell.Rows.Hidden = False Then
	'if this row gives you a error its because one of your cells has a #NA, fix the data!
	If cell.Value <> "" Then
	If Not dict.Exists(cell.Value) Then

agbishara / VBA - Find and Replace things in worksheet

Created April 15, 2014 15:57

This is good if you need to quickly replace text in a workbook for a report

 Sub FnR(sWhat As String, sReplacment As String, ws As Worksheet)
   ws.UsedRange.Replace What:=sWhat, Replacement:=sReplacment, LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
   ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, sWhat, sReplacment)
   ws.PageSetup.LeftHeader = Replace(ws.PageSetup.LeftHeader, sWhat, sReplacment)
   ws.PageSetup.RightHeader = Replace(ws.PageSetup.RightHeader, sWhat, sReplacment)
 End Sub

agbishara / VBA - Create Folder

Created April 15, 2014 15:58

This is a example of how to create sub-folders in Excel VBA

	Dim WBP As String
	Dim VPF As String

	WBP = ThisWorkbook.Path & ""
	VPF = WBP & "\Generated\VP"
	CreateFolder (VPF)

	Function CreateFolder(Fnamez As String)
	MkDir Fnamez
	End Function

agbishara / VBA - Recalculate Sheet

Last active August 29, 2015 13:59

Simple line to recalculate all workbook formulas before you run your VBA

Application.CalculateFull

agbishara / VBA - Filter Reset

Created April 15, 2014 16:01

This toggles workbook filters in Excel for quickly resetting them

	Function ResetFilters(ws As Worksheet)
	ws.Rows(1).AutoFilter
	ws.Rows(1).AutoFilter
	End Function

agbishara / VBA - Create new workbook at path

Created April 15, 2014 16:02

Function to Create a new workbook in Excell

	Function CreateWorkbook(Path As String) As Workbook
	Dim Tempbook As Workbook
	Set Tempbook = Workbooks.Add(ThisWorkbook.Path & "\template.xlsx")
	Application.DisplayAlerts = False
	Tempbook.SaveAs Filename:=Path
	Application.DisplayAlerts = True
	Set CreateWorkbook = Tempbook
	End Function

agbishara / VBA - Copy and Paste Visible Cells in Range

Created April 15, 2014 16:03

This is good if you need to filter on a worksheet and do a special copy

	Mainbook.Sheets(4).Range("E2:k5000").SpecialCells(xlCellTypeVisible).Copy
	Sheet.Range(TMPCellAddress).PasteSpecial xlPasteValues