Excel And VBA Tips

Sub data_filter() Dim k As Integer For k = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row Step 40 Sheet1.Range(Sheet1.Cells(k, "A"), Sheet1.Cells(k + 39, "A")).Copy Sheet1.Range("XFD1").End(xlToLeft).Offset(0, 1) Next End Su b Example :- data data      44,305      46,119      45,540      44,305      49,819      43,784      49,313      49,819      41,860      44,472      44,973      41,860      46,119      43,784      44,472      45,540      49,313    ...

Dim exampleDate As Date exampleDate = DateValue("Jun 19, 2010") MsgBox Year(exampleDate) DateAdd Dim firstDate As Date, secondDate As Date firstDate = DateValue("Jun 19, 2010") secondDate = DateAdd("d", 3, firstDate) MsgBox secondDate Current Date & Time MsgBox Now Hour, Minute and Second MsgBox Hour(Now) TimeValue MsgBox TimeValue("9:20:01 am")

Max Min Average Date 20 16 3/1/2001 22 17 3/2/2001 27 21 3/3/2001 29 23 3/4/2001 25 20 3/5/2001 25 21 3/6/2001 24 16 3/7/2001 23 17 3/8/2001 22 20 3/9/2001 21 26 3/10/2001 27 21 3/11/2001 20 16 3/12/2001 22 17 3/13/2001 26 20 3/14/2001 23 18 3/15/2001 29 23 3/16/2001 27 21 3/17/2001 28 22 3/18/2001 25 20 3/19/2001 '===========================================================   http://jeetexltips.blogspot.in/ '=========================================================== Sub DO_Until() ' This loop runs until there is nothing in the next column     Do     ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"     ActiveCell.Offset(1, 0).Select  ...

Imagine that (somewhat egotistically) you decide to name all of your worksheets in a workbook after your company name (for us it's  Wise Owl ).  Sub  RenameWorksheets() 'a reference to each worksheet in the active workbook Dim  ws  As  Worksheet 'the index number to use Dim  SheetNumber  As   Integer SheetNumber = 0 For   Each  ws In Worksheets 'for each worksheet, rename it SheetNumber = SheetNumber + 1 ws.Name = "Wise Owl " & SheetNumber Next  ws End   Sub

Sub dlt_Sheet()     Dim ws As Worksheet          On Error Resume Next     Application.DisplayAlerts = False     ThisWorkbook.Worksheets(" Sheet1 ").Delete     Application.DisplayAlerts = True     On Error GoTo 0    End Sub