danwagnerco’s gists

danwagnerco / twister_spinner.rb

Created July 31, 2015 02:16

Short reproduction of a friend's twister emulator

	def spinner
	colors = ["Red", "Blue", "Yellow", "Green"]
	appendages = ["Left Hand", "Right Hand", "Left Foot", "Right Foot"]

	puts "Place your #{appendages.sample} on #{colors.sample}!"
	end
	spinner

danwagnerco / slowly_deleting_rows.vb

Last active August 29, 2015 14:27

This For loop deletes rows correctly (but slowly)

	Option Explicit
	Public Sub DeleteRowsSlowly()
	Dim lngIdx As Long

	For lngIdx = 100000 To 1 Step -1
	If Cells(lngIdx, 1).Value = vbNullString Then
	Cells(lngIdx, 1).EntireRow.Delete
	End If
	Next lngIdx
	End Sub

danwagnerco / slowly_deleting_rows_that_are_less_than_50.vb

Last active August 29, 2015 14:27

This For loop deletes rows where the cell value is less than 50 (but slowly)

	Option Explicit
	Public Sub DeleteRowsLessThanFiftySlowly()
	Dim lngIdx As Long

	For lngIdx = 100000 To 1 Step -1
	If Cells(lngIdx, 1).Value < 50 Then
	Cells(lngIdx, 1).EntireRow.Delete
	End If
	Next lngIdx
	End Sub

danwagnerco / slowly_deleting_rows_that_are_older_than_a_date.vb

Last active August 29, 2015 14:27

This For loop deletes rows where the cell value is more recent than February 1st, 2013 (but slowly)

	Option Explicit
	Public Sub DeleteDatesMoreRecentThanFebFirstSlowly()
	Dim lngIdx As Long

	For lngIdx = 1000000 To 1 Step -1
	If Cells(lngIdx, 1).Value > DateValue("2/1/2013") Then
	Cells(lngIdx, 1).EntireRow.Delete
	End If
	Next lngIdx
	End Sub

danwagnerco / delete_rows_fast_with_autofilter.vb

Last active November 29, 2022 07:09

This short script deletes rows IN A HURRY by leveraging Range.Autofilter

	Option Explicit
	Public Sub DeleteRowsFastWithAutofilter()

	Dim wksData As Worksheet
	Dim rngDataBlock As Range
	Dim lngLastRow As Long, lngLastCol As Long

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

danwagnerco / extract_values_from_string_with_regexp.vb

Last active August 29, 2015 14:27

	Option Explicit
	Public Sub ExtractInfoFromSquareBrackets()

	Dim wksRaw As Worksheet
	Dim strPattern As String, strRaw As String, strMatch As String
	Dim rngAllRows As Range, rngCell As Range
	Dim lngLastRow As Long, lngIdx As Long
	Dim objMatches As Object
	Dim rgx As RegExp
	Set rgx = New RegExp

danwagnerco / transpose_horizontal_data_to_vertical.vb

Last active August 27, 2015 03:20

This macro "de-pivots" a few columns (and repeats the corresponding rows) to make forming a pivot table easy

	Option Explicit
	Public Sub TransposeHorizontalToVertical()

	Dim lngLastRow As Long, lngIdx As Long, lngOutputLastRow As Long, _
	lngDetailsIdx As Long, lngTargetRow As Long, lngTargetCol As Long
	Dim wksInput As Worksheet, wksOutput As Worksheet
	Dim varDetailNames As Variant, varMonthNames As Variant, _
	varDetails As Variant, varValues As Variant
	Dim varDetailsKey As Variant, varValuesKey As Variant
	Dim dicDetails As Scripting.Dictionary, dicValues As Scripting.Dictionary

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")

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 / 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

Dan Wagner danwagnerco