danwagnerco

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

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

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

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

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

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

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

Option Explicit
Public Sub ImportAllDataFiles()

    Dim strFile As String
    Dim wbk As Workbook
    Dim wks As Worksheet, wksMaster As Worksheet
    Dim lngLastMasterRow As Long, lngFirstMasterRow As Long, _
        lngLastDataRow As Long, lngLastDataCol As Long, lngIdx As Long, _
        lngTimeZoneCol As Long, lngDateCol As Long
    Dim rngTimeZoneCol As Range, rngDateCol As Range, _

danwagnerco

Vim, Vundle and ConEmu on Windows

Let's start by getting it out on the table: Sublime Text is great, and version 3 should no longer be considered "abandonware" as of build 3065. Sublime Text served me very, very well over the years. That said, trying out new things is a major part of leveling-up, and in that vein Vim deserves a go. (And have you seen some of those thoughtbot guys flying around in Vim? It's awesome!)

Getting Vim up-and-running on your Windows machine doesn't have to be an all-day project. In this post, we'll walk through:

• Installing gVim, which gives us both the "classic" command line version as well as the graphical version
• Installing Vundle, the best way to handle Vim-enhancing packages
• Installing ConEmu, a supercharged command line emulator for Windows

danwagnerco

Option Explicit
Public Sub DeleteBlankRows()

    Dim wks As Worksheet
    Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
        lngColCounter As Long
    Dim blnAllBlank As Boolean
    
    'First things first: we identify our basic variables
    Set wks = ThisWorkbook.Worksheets("hello")