Finding next available row issue

[Excel_noob_101]

Hello everyone, I am a brand new noobie here, I need assistance with something I am working on;

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

I need this to ignore a certain row (row G). My issue is that this macros detects formulas (or the result of the formula which is 0 since no info is input yet) however it goes to the next available row which has no formula.

Unless there is an easier way can anyone assist me with adjusting this macros to skip Row G when searching for the next available row?

-Richard

[BadlySpelledBuoy]

Hi Richard, and welcome to the forum.

What exactly is it you're trying to do? Find the last row/column? Bit confused on this because you say row, but then talk about G, which would traditionally be a column.

Perhaps posting a sample workbook with the row/column you're trying to locate highlighted would be of help in producing a swift solution.

BSB

[Excel_noob_101]

Sorry I do apologize for the confusion I didn't have enough coffee yet. This is what I have right now. It works fine however I input =SUMIF(A:A, A6, F:F) on column G the macros detects the formula and skips all the way down until it cannot detect a formula any more on Column G, is there any way to avoid this?

Please Login or Register  to view this content.

[MarvinP]

Hi Richard,

Normally the next available row is the one on the bottom of the whole sheet (in a specific column). The VBA for finding this is as follows.

Dim LastRow as Double
LastRow = ws.Cells(Rows.Count,"A").End(xlUp).Row

Note - Rows.Count is a number. It is the bottom row (do Control Down Arrow lots of times). Early Excel had less rows than the newer versions of Excel.
Note - the "A' in the formula above tells excel to go to that column. Sometimes there are blanks (at the bottom of the A column so you want B or C..)

You might then want to get to the row below the last one and that would be something like:

Cells(LastRow +1,"F").Select ' to get to the first blank row (using A column) and set the cursor in that row in the F column.

I hope that helps.

[6StringJazzer]

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[MarvinP]

Hi Richard,

To follow your code and logic correctly I need a sample workbook and the code in it. You should explain what is happening and what you expect the code to do for you.

To attach a sample workbook, Click on Go Advanced and then on the Paper Clip Icon above the advanced message area.