Skip to content

Instantly share code, notes, and snippets.

@danwagnerco
danwagnerco / list.md
Created Jan 28, 2022 — forked from ih2502mk/list.md
Quantopian Lectures Saved
View list.md
@danwagnerco
danwagnerco / clear_cell_contents_on_click.vb
Created Apr 14, 2017
Placed in the Worksheet you'd like this mode to work in, you can clear cell contents by simply clicking the cell, which allows for instant editing
View clear_cell_contents_on_click.vb
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.FormulaR1C1 = ""
End Sub
View dmikester1_mod.vb
'don't do this anymore:
DataBlock.SpecialCells(xlCellTypeVisible).Copy Destination:=Target.Cells(1, 1)
'instead try this:
Dim rngDestination As Range 'this should be way up at the top with all the other declarations
' ... all the other code
Set rngDestination = Target.Cells(1, 1)
DataBlock.SpecialCells(xlCellTypeVisible).Copy
rngDestination.PasteSpecial xlPasteType:=xlPasteValuesAndNumberFormats
@danwagnerco
danwagnerco / CombineManyWorkbooksIntoOneWorksheet.vb
Last active May 16, 2019
This VBA macro combines data from many workbooks in a single folder into a single worksheet
View CombineManyWorkbooksIntoOneWorksheet.vb
Option Explicit
Public Sub CombineManyWorkbooksIntoOneWorksheet()
Dim strDirContainingFiles As String, strFile As String, _
strFilePath As String
Dim wbkDst As Workbook, wbkSrc As Workbook
Dim wksDst As Worksheet, wksSrc As Worksheet
Dim lngIdx As Long, lngSrcLastRow As Long, _
lngSrcLastCol As Long, lngDstLastRow As Long, _
lngDstLastCol As Long, lngDstFirstFileRow As Long
@danwagnerco
danwagnerco / HighlightCorrespondingCells.vb
Created Oct 15, 2016
This macro will highlight cells in column G based on the selection in column A
View HighlightCorrespondingCells.vb
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wksLookups As Worksheet
Dim lngFirstRow As Long, lngLastRow As Long
'Get the Worksheet so we can confidently identify
'the Range that we'll be highlighting (if need be)
Set wksLookups = Target.Parent
@danwagnerco
danwagnerco / exampleEarlyBindingWithScriptingDictionary.vb
Created Oct 2, 2016
an example of early-binding in VBA (with the Scripting.Dictionary object, a personal favorite)
View exampleEarlyBindingWithScriptingDictionary.vb
Dim dicCoolDictionary As Scripting.Dictionary
Set dicCoolDictionary = New Scripting.Dictionary
' then start assembling the dictionary
View exampleEarlyBinding.vb
Dim variable as SomeObjectType
Set variable = New SomeObjectType