10 Replies Latest reply: May 15, 2014 10:13 AM by TMERRYMAN _ RSS

    Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS

    TMERRYMAN _

      Hello.  I hope someone can help me.  I am really struggling to figure out why I cannot append to a table in an existing spreadsheet in MS Excel 2007.  I keep getting the error "Row Count Exceeds Maximum...", however, the spreadsheet only has 4,427 rows in it and I'm only adding 165 more rows.

       

      Any clue what I'm missing?

       

      Thanks!

      Teresa

        • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
          RalphB _

          Teresa,

           

          It sounds like you may have some data in the area of the spreadsheet you are trying to append to.   You may want to check the named range you are trying to append to in Excel to see that the last row of the range is not past row 4427.  If it is OK, you may want to check to see if there is some data below that range.

           

          I run into similar problems when someone decides to use the space bar to delete data in a cell.  When that happens, I have to search all the cells below the range and DELETE the offending cell.  The fastest way I've found is to hit the end and then the down arrow to search down to the bottom and end up to go up.

           

          Let us know if that helps..

            • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
              TMERRYMAN _

              Thank you for replying Ralph.  I checked as you recommended and there is no data below my last row.  However, I had not thought of checking the named ranges.  Doing so, brought another issue to my attention.  When editing my project export, my "existing tables" do not come up.  Only the named ranges are showing up AS "existing tables".  Do you know why this would be?  When I deleted the named range on the spreadsheet I am trying to append, it did not show up on my "existing tables" list.  However, I added it back, and voila...it was there.  I haven't had this issue before.  I think that if I can get it to look at my tables and not my ranges, my problem would be solved.

                • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                  elginreigner _

                  Monarch works off of name ranges, not the worksheet names. This is what is causing your issue when you get the error about maximum rows. There are manual work arounds, easiest fix is to remove the file and start fresh file.

                   

                  From my experience, you will see this when a user has edited the file manually. When this happens, the name range is now off. When Monarch  attempts to insert into the name range, and increment the count by 1, it throws the error because of the current row count.

                   

                  Hope this helps.

                    • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                      TMERRYMAN _

                      Okay, I see now.  Actually, this is a file that users will be editing manually and also appending new data to it daily.  Is this not going to work for us?

                       

                      Monarch works off of name ranges, not the worksheet names. This is what is causing your issue when you get the error about maximum rows. There are manual work arounds, easiest fix is to remove the file and start fresh file.

                       

                      From my experience, you will see this when a user has edited the file manually. When this happens, the name range is now off. When Monarch  attempts to insert into the name range, and increment the count by 1, it throws the error because of the current row count.

                       

                      Hope this helps.[/QUOTE]

                        • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                          RalphB _

                          Sorry about not getting back sooner.  I have excel files that I append to every day and others work them and have the same problems.  I finally have most people trained to if they need to append data to one of those files to insert it above the last row.  That allows them to add data and adjust the named range automatically.  Another possible problem is when they delete data.  If they are deleting data at the bottom of the range, they need to delete the entire row and not just the data in the cells as Monarch looks at the bottom of the named range and if it finds blank cells it will not append.

                            • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                              TMERRYMAN _

                              Thanks for your responses.  It's very appreciated.  However, I don't think this is a solution for our users.  I need something simple for them that consistently works. 

                               

                              Sorry about not getting back sooner.  I have excel files that I append to every day and others work them and have the same problems.  I finally have most people trained to if they need to append data to one of those files to insert it above the last row.  That allows them to add data and adjust the named range automatically.  Another possible problem is when they delete data.  If they are deleting data at the bottom of the range, they need to delete the entire row and not just the data in the cells as Monarch looks at the bottom of the named range and if it finds blank cells it will not append.[/QUOTE]

                            • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                              elginreigner _

                              Okay, I see now.  Actually, this is a file that users will be editing manually and also appending new data to it daily.  Is this not going to work for us?[/QUOTE]

                               

                              I process daily payment files that ran into this issue. Our resolution, since they only needed to modify the files that day, is to create a copy of the processed file. Do you need to keep the data they append?

                               

                              RalphB, We tried training the users as well, got to be a hassle. They would either forget or we would get new users in that needed to be trained.

                                • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                                  KeyserSoze _

                                  This problem might be avoided by running an Excel macro on the file that Monarch exports (and that the users may have manipulated) before once again updating the file with Monarch.

                                   

                                  'Copy this macro to a module in the PERSONAL.XLSB or PERSONAL.XLS workbook, and assign it to a custom toolbar icon

                                  'Then open the exported Monarch file in Excel and click the toolbar icon to run this macro

                                  Sub CheckMonarchRange()

                                  Dim lLastRow As Long

                                  Dim lLastColumn As Long

                                  Dim bAdjustRange As Boolean

                                   

                                      'Check that there's a workbook open

                                      'A count of 1 means that only the personal macro workbook is open,

                                      'therefore there's nothing to work on, so stop right now

                                      If Workbooks.Count = 1 Then Exit Sub

                                       

                                      'Get the last row and column info

                                      'Assumes that values exist in every cell in the first row and first column

                                      lLastRow = Range("A1").End(xlDown).Row

                                      lLastColumn = Range("A1").End(xlToRight).Column

                                       

                                      'No, we don't need adjust the Monarch range yet

                                      bAdjustRange = False

                                       

                                      'Monarch always creates a named range with the same name as the worksheet name

                                      'Check to see if the rows used have changed

                                      If Range("A1", Range(ActiveSheet.Name)).Rows.Count <> lLastRow Then

                                          bAdjustRange = True

                                      End If

                                       

                                      'Check to see if the columns used have changed

                                      If Range("A1", Range(ActiveSheet.Name)).Columns.Count <> lLastColumn Then

                                          bAdjustRange = True

                                      End If

                                       

                                      If bAdjustRange Then

                                          With ActiveWorkbook

                                              .Names.Add Name:=ActiveSheet.Name, RefersTo:=Range("A1", Range("A1").Offset(lLastRow - 1, lLastColumn - 1))

                                              'Suppress Excel dialog box

                                              Application.DisplayAlerts = False

                                              .Save

                                              'Enable regular Excel dialogs

                                              Application.DisplayAlerts = True

                                          End With

                                      End If

                                  End Sub

                                  /CODERun the macro, close Excel, and carry on with Monarch work as normal. This avoids the issues created by relying on the Excel file users.

                                    • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                                      TMERRYMAN _

                                      I'm going to try this...

                                       

                                      This problem might be avoided by running an Excel macro on the file that Monarch exports (and that the users may have manipulated) before once again updating the file with Monarch.

                                       

                                      'Copy this macro to a module in the PERSONAL.XLSB or PERSONAL.XLS workbook, and assign it to a custom toolbar icon

                                      'Then open the exported Monarch file in Excel and click the toolbar icon to run this macro

                                      Sub CheckMonarchRange()

                                      Dim lLastRow As Long

                                      Dim lLastColumn As Long

                                      Dim bAdjustRange As Boolean

                                       

                                          'Check that there's a workbook open

                                          'A count of 1 means that only the personal macro workbook is open,

                                          'therefore there's nothing to work on, so stop right now

                                          If Workbooks.Count = 1 Then Exit Sub

                                           

                                          'Get the last row and column info

                                          'Assumes that values exist in every cell in the first row and first column

                                          lLastRow = Range("A1").End(xlDown).Row

                                          lLastColumn = Range("A1").End(xlToRight).Column

                                           

                                          'No, we don't need adjust the Monarch range yet

                                          bAdjustRange = False

                                           

                                          'Monarch always creates a named range with the same name as the worksheet name

                                          'Check to see if the rows used have changed

                                          If Range("A1", Range(ActiveSheet.Name)).Rows.Count <> lLastRow Then

                                              bAdjustRange = True

                                          End If

                                           

                                          'Check to see if the columns used have changed

                                          If Range("A1", Range(ActiveSheet.Name)).Columns.Count <> lLastColumn Then

                                              bAdjustRange = True

                                          End If

                                           

                                          If bAdjustRange Then

                                              With ActiveWorkbook

                                                  .Names.Add Name:=ActiveSheet.Name, RefersTo:=Range("A1", Range("A1").Offset(lLastRow - 1, lLastColumn - 1))

                                                  'Suppress Excel dialog box

                                                  Application.DisplayAlerts = False

                                                  .Save

                                                  'Enable regular Excel dialogs

                                                  Application.DisplayAlerts = True

                                              End With

                                          End If

                                      End Sub

                                      /CODERun the macro, close Excel, and carry on with Monarch work as normal. This avoids the issues created by relying on the Excel file users.[/QUOTE]

                                    • Row Count Exceeds Maximum...error ONLY HAVE 4,427 ROWS
                                      TMERRYMAN _

                                      Actually, it will be a new file each month that they will append current data to and manually enter information into daily.

                                       

                                      I process daily payment files that ran into this issue. Our resolution, since they only needed to modify the files that day, is to create a copy of the processed file. Do you need to keep the data they append?

                                       

                                      RalphB, We tried training the users as well, got to be a hassle. They would either forget or we would get new users in that needed to be trained.[/QUOTE]