danwagnerco’s gists

danwagnerco / delete_rows_with_autofilter.vb

Last active January 28, 2016 20:37

This macro deletes rows using the Range.AutoFilter method (instead of a For loop)

	Option Explicit
	Public Sub DeleteRowsWithAutofilter()

	Dim wksData As Worksheet
	Dim lngLastRow As Long
	Dim rngData As Range

	'Set references up-front
	Set wksData = ThisWorkbook.Worksheets("data")

danwagnerco / delete_rows_with_for_loop.vb

Last active January 22, 2016 13:00

This script deletes rows that match "Invoice.zip" or "Thumbs.db"

	Option Explicit
	Public Sub DeleteRowsWithForLoop()

	Dim wksData As Worksheet
	Dim lngLastRow As Long, lngIdx As Long

	'Set references up-front
	Set wksData = ThisWorkbook.Worksheets("data")

	With wksData

danwagnerco / convert_lookups_to_values.vb

Created January 21, 2016 04:31

This macro converts successful VLOOKUP formulas into values (thereby "locking" them) in case your lookup table has to change

	Option Explicit
	Public Sub ConvertLookupsToValues()

	Dim lngLastRow As Long, lngIdx As Long
	Dim varLookups As Variant

	'Everything happens on the Ledger sheet
	With ThisWorkbook.Worksheets("Ledger")

	'Identify the last-occupied row in column D on the Ledger sheet,

danwagnerco / zip_folders_in_column.vb

Last active January 15, 2021 20:52

This script loops through a column and creates zip files for each folder listed using 7-Zip

	Option Explicit
	Public Sub ZipFoldersInColumn()

	Dim wks As Worksheet
	Dim lngIdx As Long, lngErrorCode
	Dim strFullPath As String, strZipName As String, strEndFound As String, _
	strCommand As String, strTargetDir As String
	Dim blnEndFound As Boolean
	Dim wsh As WshShell
	Set wsh = New WshShell

danwagnerco / run_batch_file_then_rename_resulting_folder.vb

Last active September 20, 2015 02:20

This script runs a batch file, but waits for that the batch file to finish before moving on (and eventually modifying the results of the batch file)

	Option Explicit
	Public Sub RunBatchFileThenRenameFolder()

	Dim strNewFolderName As String, strRunBatchCommand As String, _
	strRenameFolderCommand As String
	Dim lngErrorCode As Long
	Dim wks As Worksheet
	Dim wsh As WshShell
	Set wsh = New WshShell

danwagnerco / run_batch_file.vb

Last active September 20, 2015 02:02

This script runs a batch file from VBA using the WshShell object

	Option Explicit
	Public Sub RunBatchFile()

	Dim strCommand As String
	Dim lngErrorCode As Long
	Dim wsh As WshShell
	Set wsh = New WshShell

	'Run the batch file using the WshShell object
	strCommand = Chr(34) & _

danwagnerco / toolbelt_release_checklist.md

Last active September 20, 2015 17:50

Checklist for toolbelt release

Copy A_Functions to a new workbook
Copy C_Pearson to the same workbook
Add the following references:
- Microsoft Scripting Runtime
- Microsoft VBScript Regular Expressions 5.5
- Windows Script Host Object Model
Change VBAProject to toolbelt
Set version in Project Properties > Project Description
Put the file in Dropbox > toolbelt
Email subscribers to let them know about the update

danwagnerco / move_data_based_on_drop_down.vb

Last active September 6, 2015 11:50

This script moves data on the 'Allocate' sheet to a dynamic destination sheet based on user input

	Option Explicit
	Public Sub MoveDataBasedOnDropDown()

	Dim strInput As String, strPromptMessage As String
	Dim wksAllocate As Worksheet, wksTarget As Worksheet
	Dim obj As Object
	Dim lngAllocateLastRow As Long, lngAllocateLastCol As Long, _
	lngTargetLastRow As Long
	Dim rngAllocate As Range, rngTarget As Range

danwagnerco / save_sheets_as_pdf.vb

Last active September 4, 2015 13:46

This short script creates a single PDF from a three-sheet Workbook

	Option Explicit
	Public Sub SaveSheetsAsPDF()

	Dim wksAllSheets As Variant
	Dim wksSheet1 As Worksheet
	Dim strFilename As String, strFilepath As String

	'Set references up-front
	Set wksSheet1 = ThisWorkbook.Sheets("Sheet1")
	wksAllSheets = Array("Sheet1", "Sheet2", "Sheet3")

danwagnerco / create_subset_workbook_based_on_dates.vb

Last active July 5, 2020 17:12

This script creates a new workbook containing ONLY data between the input dates

	Option Explicit

	'This subroutine prompts the user to select dates
	Public Sub PromptUserForInputDates()

	Dim strStart As String, strEnd As String, strPromptMessage As String

	'Prompt the user to input the start date
	strStart = InputBox("Please enter the start date")

Dan Wagner danwagnerco