Find Next Row With No Value In It - But Not Next Row With No Formula In It

I've got this

   nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
searchorder:=xlByRows).Row + 1
    Cells(nextrow, 1).Select

which will find nextrow with nothing in it and select cell in Col A,
but now I've got formulas in the cells, so instead of finding the next
row with nothing in it, I want to find the next row with no value -
even if it's got a formula in it.  Can I do this by amending what's in
the what:="*" part of this code?
0
robzrob
3/5/2010 11:19:13 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
948 Views

Similar Articles

[PageSpeed] 24

It's always better to include all the parms for .find().  If you don't, then
you're at the mercy of the previous Find--either by the user or by your (or
other's) code.

And one of those "hidden" options is that you're looking through formulas.  If
you specify values, then you may see it work ok.

Another problem with your snippet of code is if there isn't anything found.  You
won't be able to add 1 to the row number (since the cell wasn't found and it has
no row!).

I'd use something like:

Option Explicit
Sub testme()

    Dim NextRow As Long
    Dim FoundCell As Range
                
    With ActiveSheet
        Set FoundCell = .Cells.Find(what:="*", _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
                            
        If FoundCell Is Nothing Then
            NextRow = 1 'top row
        Else
            NextRow = FoundCell.Row + 1
        End If
                       
        .Cells(NextRow, 1).Select
        
    End With
    
End Sub

And the asterisk represents a wildcard--any set of characters.  

Cells(1) is the first cell (A1 in this example).  

robzrob wrote:
> 
> I've got this
> 
>    nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious,
> searchorder:=xlByRows).Row + 1
>     Cells(nextrow, 1).Select
> 
> which will find nextrow with nothing in it and select cell in Col A,
> but now I've got formulas in the cells, so instead of finding the next
> row with nothing in it, I want to find the next row with no value -
> even if it's got a formula in it.  Can I do this by amending what's in
> the what:="*" part of this code?

-- 

Dave Peterson
0
Dave
3/5/2010 1:31:15 PM
On Mar 5, 1:31=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> It's always better to include all the parms for .find(). =A0If you don't,=
 then
> you're at the mercy of the previous Find--either by the user or by your (=
or
> other's) code.
>
> And one of those "hidden" options is that you're looking through formulas=
.. =A0If
> you specify values, then you may see it work ok.
>
> Another problem with your snippet of code is if there isn't anything foun=
d. =A0You
> won't be able to add 1 to the row number (since the cell wasn't found and=
 it has
> no row!).
>
> I'd use something like:
>
> Option Explicit
> Sub testme()
>
> =A0 =A0 Dim NextRow As Long
> =A0 =A0 Dim FoundCell As Range
>
> =A0 =A0 With ActiveSheet
> =A0 =A0 =A0 =A0 Set FoundCell =3D .Cells.Find(what:=3D"*", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 After:=3D.Cells(1=
), _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LookIn:=3DxlValue=
s, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LookAt:=3DxlWhole=
, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SearchOrder:=3Dxl=
ByRows, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SearchDirection:=
=3DxlPrevious, _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 MatchCase:=3DFals=
e)
>
> =A0 =A0 =A0 =A0 If FoundCell Is Nothing Then
> =A0 =A0 =A0 =A0 =A0 =A0 NextRow =3D 1 'top row
> =A0 =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0 =A0 NextRow =3D FoundCell.Row + 1
> =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 .Cells(NextRow, 1).Select
>
> =A0 =A0 End With
>
> End Sub
>
> And the asterisk represents a wildcard--any set of characters. =A0
>
> Cells(1) is the first cell (A1 in this example). =A0
>
> robzrob wrote:
>
> > I've got this
>
> > =A0 =A0nextrow =3D Cells.Find(what:=3D"*", searchdirection:=3DxlPreviou=
s,
> > searchorder:=3DxlByRows).Row + 1
> > =A0 =A0 Cells(nextrow, 1).Select
>
> > which will find nextrow with nothing in it and select cell in Col A,
> > but now I've got formulas in the cells, so instead of finding the next
> > row with nothing in it, I want to find the next row with no value -
> > even if it's got a formula in it. =A0Can I do this by amending what's i=
n
> > the what:=3D"*" part of this code?
>
> --
>
> Dave Peterson


Thanks - I'll try that in my next workbook - I'm a bit wary of taking
out sections of code and replacing them - don't want the whole thing
to go haywire.  But you've shown me how it works, so I know what to
do, ie insert LookIn:=3DxlValues in my current code.  Thanks.
0
robzrob
3/5/2010 6:38:27 PM
Reply:

Similar Artilces:

Excel VBA
Hi VBAers, I need to copy autofilter rows from one worksheet to another (within the same workbook). My autofilter code is: Columns("W:W").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="TRUE" I'm not sure how to do the following: 1) code to copy the rows found to the other worksheet 2) how to cater for the situation if no rows are found. I've researched but all the help Google returns is rather confusing. Can someone please advise. On Feb 16, 2:43=A0pm, kazzy <kazm...@gmail.com> wrote: > Hi VBAers, > > I ne...

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Sub to check and report any formula returned error (eg: #REF!)
I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae, and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell. If there are errors, msg will list the affected codenames. Thanks hi, Max ! > I need a sub to run a quick check through a list of sheets (using their codenames) filled with a ton of formulae > and pop up an all clear msg if there are no errors (eg: #REF!) returned in any formula cell. > If there are errors, msg will list the affected codenames. Thanks try with som...

Copy Userform ListBox AND TextBox values
How can I copy the values from ListBox1 and TextBox2 from the same Userform into the same cell on a worksheet? I have the code to copy either one to the correct location but I need to merge the values from both, seperated by a slash (/). I have tried using the following but it will paste "FALSE" into the appropriate cell: With Target .Value = ListBox1.Value & " / " & .Value = TextBox2.Text 'copies data to column B End With Thanks. -- Traa Dy Liooar Jock Ignore last - I have managed to do it. -- Traa Dy Liooar Jock ...

Autofill of row or column
Greetings, Let's say I have a min, a max, and an increment, all decimal values. I want to autofill or autopopulate a row or column -- without manually dragging cels -- over the range [min,max] incrementing by the increment value each cell. Can this be done somehow? Also, what if you don't know how many cells you will need initially (you return some variable integer from a calculation)--can you tell Excel to fill only that number of cells each time? For example, let's say I compute some number and get a 7 one time and a 10 the next, and I want 7 cells filled with data and then ...

Absolute Values and Column Insertions
I've created a formula in column A that adds up some cells in column D. Ex: =sum(D3:D6). If I insert one column in front of column D, the formula changes to =sum(E3:E6). This happens even if I change the formula to =sum($D$3:$D$6). I also tried creating a range name called TEST referring to the original $D$3:$D$6 range and the column insertion changed the TEST range definition as well. Is there a way to keep a formula from NOT adjusting to column insertions or deletions? Thanks in advance! Please don't multi-post See answer in other newsgroup -- Kind regards, Niek Otten Mic...

uninstalling microsoft office 2001 value pack
Hi, I already have office 2001 on my machine, and recently installed the value pack. i noticed word became notably slower and so for now i just wish to unistall the value pack. how can i do that cleanly. any ideas why my machine would slow down like that. i still do have 1.2 Gig of hard disk space available. thanks. ...

Copy a file with only values no formulas
Is there an easy way to copy a workbook so that it only has the values and not the formulas? Thank you. -- Lee Coleman Right click on a sheet tab and select all sheets. Then select all cells on the active sheet. Do a copy, then Edit, paste special, values. Right click on a sheet tab and select ungroup sheets. Save the workbook under a different name!!!! Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Lee Coleman" <lee@nospam.jjlgreenhouses.com> wrote in message news:OIOx4$FeHHA.4020@TK2MSFTN...

How to average a column, but exclude zero AND negative values?
I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero values, but what about zero AND Negative? Thanks!! =AVerage(IF(A1:A10)>0,A1:A10)) Still array entered. KMHarpe wrote: > > I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero > values, but what about zero AND Negative? > > Thanks!! -- Dave Peterson ...

IF String value the....
I am trying to calculate a formula based on the right most character in a cell. For instance if a cell had a entry like Baker T. I want to calculate a formula if the T is there and another if the T is missing. Thanks in advance Hi use =IF(RIGHT(A1,1)="T","do something","something else") -- Regards Frank Kabel Frankfurt, Germany jolene wrote: > I am trying to calculate a formula based on the right most character > in a cell. > For instance if a cell had a entry like Baker T. I want to calculate > a formula if the T is there and another if the...

Request To Shorten A Formula
Hello, don't want to be pest on my Duplicate Questions, but I was hoping someone could give me a shorter formula for the one I have here. I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender in which I check for dupes in the Phone Number Column E6-E35 in all sheets, This is the Formula that works, but I want it shorter if Possible.. Thanks for any Help: =SUM(COUNTIF(INDIRECT("DAY"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"!E6:E35"),"="&E6)) -- Mhz ----------------------------------...

Automatic Calculation of Table Formulas
Is there a way to set Word so that a target cell automatically re-calculates whenver any of the cells used in the formula is changes? Only if the components of the formula are provided from the results of form fields in a protected form and you have the calculate on exit check box properties of the fields checked. Otherwise you would have to force an update of the fields in the calculation. You can do that with a macro, such as that used as an example at http://www.gmayor.com/installing_macro.htm Alternatively you can insert an Excel table and have the full panoply of Excel fu...

How to Link to the next blank row after the last entry in a column?
Cell BM6 Sheet1, has a formula that gives me a numeric total. The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry. Sheet1 is a work sheet, which will be saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record. The numbers on original Sheet1 will then be erased and the worksheet reused for the new month. :confused: How do I get the values into the appropriate row of Sheet2 and have them remain there when I reuse the workshee...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

print column labels with one row of data at a time.
I am a teacher. I have created my gradebook in excel. (I don't have access). I have 7 worksheets (one for each class that I teach). The worksheets are not exactly the same. I would like to be able to print out the top two rows (column labels) with each students data (row). This way the student can see row 1 (name of assignment), row 2 (points), their row (their personal data for each assignment). Any help?? Go to File > Page Setup > Sheet Then in the "Rows to repeat at top" highlight the label headings row(s). HTH -- _______________________ Naz, London...

Use query for Row Source?
I attempted to use a combo-box to look-up records on a form. The RowSource query that is generated by the wizard included 2 data fields. I wanted to show only a distinct list of the second, non-key data element (col1). When I changed the RowSource query to SELECT distinct Col1, the combo-box displays nothing. I then created a stored query with the SELECT distinct Col1. They query runs correctly, but when I put in in the RowSource, it displays nothing. Any clue what I could be doing wrong? On Fri, 26 Oct 2007 12:17:03 -0700, JHC wrote: > I attempted to use a combo-box to look-u...

How do you find windows shared folders?
I am writing a piece of software to back up a shared folder on one computer to another computer. I need a list of the shared folders available on the network I am connected to by ethernet. The computers are both windows machines... One is a Windows 98 computer and the other (the one that is doing the query) is windows XP. I am writing in VB6 and Visual C++ (I do OCXs in C++) I am comfortable writing socket level code to broadcast a discovery packet if that is necessary, and parse the response results, but if there is already an API for this then of course that would be better. Thanks. B...

Copy/Paste with Hidden Rows/Columns Excel 2007
I am working with rather large & detailed spreadsheets. I have hidden rows and columns with supporting details that feed into the viewable results (formulas). I need to take the “zipped up” data and copy/paste to a new worksheet. When I paste the data, it appears with all of the hidden columns and rows visible one again. Is there any way to paste the zipped up data without the reappearance of the previously hidden rows and columns? Can you paste a link without all of the hidden data reappearing? Select range to copy which includes hidden rows and columns. F5>Special...

Paste Formulas Only
How can you copy a range and then paste cell contents only if the cell contents is a formula (cell contents begins with "=")? Pick up only the formulas select the range to copy, then do Edit=>Goto =>Special and select formulas then do your copy. -- Regards, Tom Ogilvy "Brett" <Brett@discussions.microsoft.com> wrote in message news:F577C7FC-D529-4BD2-8182-A14AFB82F595@microsoft.com... > How can you copy a range and then paste cell contents only if the cell > contents is a formula (cell contents begins with "=")? Isn't it so that it ...

Formula #32
I need a formula for this: If C1 is blank and B1 is less than 4/03/2008 count as 1. Answer: 2 A B C 1 Almond Joy 04/01/2008 2 Baby Ruth 04/02/2008 3 MARS 03/28/2008 04/01/2008 4 Mounds 04/10/2008 04/15/2008 5 Reeses 05/01/2008 6 Starburst 03/25/2008 03/31/2008 Thank you! Try this: =3DSUMPRODUCT((C1:C10=3D"")*(B1:B10<--"4/03/2008")) Adjust the ranges to suit. Hope this helps. Pete On Sep 2, 10:08=A0pm, Accesscrzy <Accessc...@discussions.microsoft.com> wrote: > I need a fo...

dragging formula
I have a small formula that wont drag down the column. The answer of the formula is what is dragging. How do I fix this so I have the correct answer in each cell? =left(a3,5) mary Are you getting an error? What happens when you try and do it? Are the cells blank afterwards? Is the worksheet/cell protected? The formula looks fine. If you are dragging by the little black cross in the bottom right hand corner of the cell, there should be no problems. Andy. "mary kay dougherty" <marykay.dougherty@necsa.com> wrote in message news:066501c38f49$d2d20670$a001280a@phx.gbl.....

Event 6398
I got the error 6298 below every 5 minutes on my Sharepoint server (MOSS 2007) Tried couple of things to no avail. Your help is greatly appreciated. Bill --------------------------------------- Log Name: Application Source: Windows SharePoint Services 3 Date: 12/11/2009 11:00:04 AM Event ID: 6398 Task Category: Timer Level: Error Keywords: Classic User: N/A Computer: WEBPORTAL.jacooil.com Description: The Execute method of job definition ScheduleAlertJob.ScheduleAlertJobDefinition (ID 2d1ee7c5-0ee5-46b9-9047-b35bf78afda2) threw an exception. More information is...

Excel XP automatic equals sign when formula bar clicked
In Excel 2000, you could click on a cell, then click on the formula bar and an equals sign would pop up in anticipation of a formula to be written. How can you make Excel 2002 duplicate this behavior? Hi Mike! This was removed from Excel 2002 onwards. The closest you'll get is: View > Toolbars > Customize Under the Insert list you'll find "=" Drag it to a toolbar near the formula bar. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. &q...

dafault value for date not working with Date() but Now() is workin
Got a table with existing data, with simple date field on a form that I want to default to todays date. I have tried inputting the Date() into the table default value for the date, but I get an error that says "Unknown function in 'Date' in validation expression or default value on 'Data.date'. Data is the table name. If I enter Now() into the default value for date, it works just fine. But, the reports that are generated, don't show the records with the date and time in them. I can go back to the table and manually delete the time from those records...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...