FANDOM


Clear contents of a rangeEdit

ThisWorkbook.ActiveSheet.Columns("A:C").Select
   Selection.ClearContents

Set a cellEdit

ThisWorkbook.ActiveSheet.Range("A5").Value = "hello"

Auto fit a columnEdit

ThisWorkbook.ActiveSheet.Columns("A:A").EntireColumn.AutoFit

Create Excel sheet from non-Excel appEdit

Dim e As New Excel.Application: e.Application.Visible = True
   Dim w As Excel.Workbook: Set w = e.Workbooks.Add
   w.ActiveSheet.Range("A5").Value = "hello"

Replace contents of a table with contents of a .csv fileEdit

Get table:

   Dim s As Worksheet: Set s = Application.ActiveSheet
   If s.ListObjects.Count <> 1 Then Stop    
   Dim l As ListObject: Set l = s.ListObjects(1)

Delete current content of table:

   l.DataBodyRange.Rows.Delete
   Do While l.ListColumns.Count > 1
       l.ListColumns(l.ListColumns.Count).Delete
   Loop

Open text file:

 Dim mytextfile As Workbook:  Set mytextfile = Workbooks.Open(csv)

Copy to the table:

mytextfile.Sheets(1).Range("A1").CurrentRegion.Copy l.HeaderRowRange

Close text file sheet:

mytextfile.Close False

Autofit the sheet.

l.Parent.Cells.EntireColumn.AutoFit

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.