How can I build a macro to auto detect number of rows?

**Spawn10** · 11-29-2011, 05:02 PM

I am new to excel programming and macros...I am trying to create a macro that will extract data in certain fields and cells, into another sheet. Right now, my approach has been using the recorder functionality to record the steps required to carry out the task. However, I have run into a snag...I have data for different months in the year and each one of them has a different number of rows, to correspond with the number of days in each month. Right now, when I do the recorder for a month that has 31 days, I record 31 rows, but when i try to run the macro for a month that is less than 31 days, i get extra data from the last 31 one day month...filling in the extra days up to 31. As an example, when I run the macro for march data, I get the complete 31 rows. When I then run it for Aprils' data, I still get the extra 31st row with the data from march on there. Rows 1 through 30 will have Aprils' data, but row 31 still shows up...with the data for march.
I guess my question is this...is there a way to program this to auto detect how many rows are available on the sheet that holds the raw data? That way, it can always grab the right number of rows for each month...and only those days for that particular month?

I also open to a new approach, as I know mine might not be the best.

Thanks,

**Trebor76** · 11-29-2011, 05:49 PM

For reference, cross posted here

**shg** · 11-29-2011, 05:50 PM

Please take a few minutes to read the rules about cross-posting (an in general).

What code do you have now?

**JohnM3** · 11-29-2011, 11:30 PM

Hi Spawn

What follows may _not_ be the best or even correct answer to your needs.

However, it will make you aware of the CurrentRegion property of the range object
which may or may not be a good way to tackle your problem.

'http://msdn.microsoft.com/en-us/library/aa214248(v=office.11).aspx

Below is some code that gets the row counts for the months Jan Feb Mar Apl

Again, this may not be the best or even a correct solution for what you need.

This code was tested and works as intended.

Please read the comments in this code.

regards
John

Sub Main()

    'The PURPOSE of all the jibberish below is to use something
    'called the CurrentRegion
    
    'It (CurrenRegion) may or may _not_ be applicable to your needs
    'But it can't hurt knowing about it
    'Google => Excel CurrentRegion

    '------------------
    '   The name of the Worksheet we are after
    '------------------
    Dim SheetName As String
    
    '------------------
    '   The Worksheet we are after
    '------------------
    Dim oWorkSheet As Excel.Worksheet
    
    '------------------
    '   A Range contained within the Worksheet
    '------------------
    Dim oMonthlyRange As Excel.Range

    '------------------
    '   Number of Rows in the month
    '------------------
    Dim NumberOfRowsInTheMonth As Long

    '------------------
    '   Starting Locations of Months
    '------------------
    Dim Jan_StartLocation As String
    Dim Feb_StartLocation As String
    Dim Mar_StartLocation As String
    Dim Apl_StartLocation As String

    '------------------
    '   Fill in the variables above
    '------------------
    SheetName = "Sheet1"  '<-------- use your real WS Name

    '------------------
    '   Set the worksheet variable to point to the
    '------------------
    Set oWorkSheet = Sheets(SheetName)
    oWorkSheet.Select

    'It is BAD to use SELECT but lets ignore 
    'At this point the ActiveSheet is now Sheet1
    
    '------------------
    '   Provide the Starting Cell Location
    '------------------
    '   The Upper Left Cell of each month
    '   Change as needed
    '------------------
    Jan_StartLocation = "B3"   
    Feb_StartLocation = "B36"
    Mar_StartLocation = "F3"
    Apl_StartLocation = "F36"
    
    '------------------
    '   Rules - or this will FAIL
    '------------------
    '*
    '*  There must be a BLANK ROW between months
    '*
    '*  There must be a BLANK COLUMN between months
    '*
    '*  If not possible then we need a different approach which is
    '*  beyond the scope of this subroutine
    '*
    '*  I am guessing based on your post that you _already_ have each months
    '*  glob of data segregated by blank rows and columns
    '*
    '------------------
    
    
    '------------------
    '   Get the range of data for a given month
    '   We will use the CurrenRegion property of the Range Object
    '------------------
    Set oMonthlyRange = oWorkSheet.Range(Jan_StartLocation).CurrentRegion

    '------------------
    '   The above says
    '
    '   Set (Point) a range object at the Worksheet Object (oWorkSheet) we already defined
    '
    '   Then
    '
    '   Get all CONTIGUOUS (I don't know how to spell) data (rows and columns)
    '   that are "attached" "near" "besides"  the cell Jan_StartLocation
    '
    '------------------

    '------------------
    '   Finally our answer
    '------------------
    NumberOfRowsInTheMonth = oMonthlyRange.Rows.Count

    '------------------
    '   For Apl - no comments
    '------------------
    Set oMonthlyRange = oWorkSheet.Range(Apl_StartLocation).CurrentRegion
    NumberOfRowsInTheMonth = oMonthlyRange.Rows.Count

    '------------------
    '   For Feb - no comments
    '------------------
    Set oMonthlyRange = oWorkSheet.Range(Feb_StartLocation).CurrentRegion
    NumberOfRowsInTheMonth = oMonthlyRange.Rows.Count

    '------------------
    '   For Mar - no comments
    '------------------
    Set oMonthlyRange = oWorkSheet.Range(Mar_StartLocation).CurrentRegion
    NumberOfRowsInTheMonth = oMonthlyRange.Rows.Count


End Sub

HTH

[code]