Rowcount overflow in vba

Question

I am using below code and this is getting overflow ,

     Dim RowCount As Integer
     RowCount = 1
     For Each rw In sh.Rows
     If sh.Cells(rw.Row, 1).Value = "Engg" Then
      r = RowCount
      addition (r) 'function
      End If
      RowCount = RowCount + 1 ' here rowcount is getting overflow.

How can I take Each loop with only avialable rows in sheet

Hello again. How did you define RowCount? Shouldn't overflow if the type is chosen wisely. Did you read the [link](http://stackoverflow.com/a/11169920/5350831) I posted about how to get last row? You might also want to look at this post: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — Vincent G, Sep 16 '16 at 10:28
@VincentG Hey ! Edited my question. But using that LastRow I can not loop in Each case .. unable to use that in each case — Pink Pink, Sep 16 '16 at 10:34
@ShaiRado Hello Sir ! But anyways Can I only loop For Each upto avialable rows only? any possibility? unlike checking for all rows in sheet, As you know my excel is just having below 10 rows only... but For Each rw In sh.Rows is seraching for whole sheet of rows — Pink Pink, Sep 16 '16 at 10:44
If you don't have any empty rows, you can loop through your sheet row per row untill you find an empty one. If not, you should select your bottom most cell (let's say row 65536), verify if it's empty (if not, you have 65536 rows) and execute Range("A65536").End(xlUp).Row — Michaël Benjamin Saerens, Sep 16 '16 at 10:48
@MichaëlBenjaminSaerens For Each rw In sh.End(xlUp).Row This is not working in For Each — Pink Pink, Sep 16 '16 at 10:51
you need to load up the rest of your code, you need to define `rw` range only to the relevant columns and rows, and then look inside this loop — Shai Rado, Sep 16 '16 at 11:07
I've tested the code as provided (more or less) and I get an overflow with rowcount as integer, and not with rowcount as long. so check your code again, Shai is correct on that score. What others are trying to tell you, I think, is that you should determine the bottom of the range first, and use a For Next loop instead of For Each. For example, shBottom = sh.cells(rows.count,1) end(xlup).row: For shcounter = 1 to shbottom: do stuff: next shcounter. — Hrothgar, Sep 16 '16 at 12:12
@SwethaReddy You don't need to do it for each row, only once to find out which row it the last. `DataRowCount = wsSheet.Cells(Rows.Count, nBasedColumn).End(xlUp).Row`works for 1 specific column, or `LastDataRow = wsSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False).Row` works independant of the columns — Michaël Benjamin Saerens, Sep 18 '16 at 21:52

score 0 · Answer 1 · answered Sep 18 '16 at 21:55

Use DataRowCount = wsSheet.Cells(Rows.Count, nBasedColumn).End(xlUp).Row when you want to know the exact amount of rows for a specific column LastDataRow = wsSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False).Rowgives you the last populated row, independant of which column

Rowcount overflow in vba

1 Answers1