last month in VBA Excel

Question

I have a table with a large amount of information, how do i select just the last months worth? (ie just the last 31 cells in the column?)

The data is in the form

date1       numbers
date2       numbers
.             .
.             .
.             .
daten       numbers

where date1 is dd/mm/ccyy

cheers

Answer 1

use

LastRow = Sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

then you can get the range from LastRow-31 to LastRow if you have the date as specified then move the start point forward until the date value = date(if(now.month=1,now.year-1,now.year), if(now.month=1,12,now.month-1), now.day)

Answer 2

Ideally there would be a column that has the date in it. Then you could do an advanced filter to filter on the date range that you require. Selecting the last 31 days will not always select just one month. It may select up to 3 days from the previous month as well.

---

Public Sub selectLastMonth()
  Dim ws As Worksheet
  Dim dStart As Date, dEnd As Date

  Set ws = ActiveSheet
  ws.Range("A:B").Sort key1:=ws.Range("A2"), header:=xlYes

  dEnd = ws.Range("A1").End(xlDown).Value
  dStart = DateSerial(DatePart("yyyy", dEnd), DatePart("m", dEnd), 1)

  ws.Range("A:B").AutoFilter field:=1, Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd

  Set ws = Nothing
End Sub