Clever Geek Handbook

Last month in VBA Excel

I have a table with a lot of information, how can I select only the last months? (i.e. only the last 31 cells in a column?)

The data is in the form

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

where date1 is dd / mm / ccyy

amuses

+1


source to share


2 answers


Ideally there will be a column with a date in it. Then you can make an advanced filter to filter on the date range you want. Selecting the last 31 days will not always select only one month. He can also choose up to 3 days from the previous month.



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
+2


source


using

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


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

+1


source






More articles:


All Articles