It is better to use xlByRows instead of xlByColumns because in case xlByColumns searching goes by columns, for example if you will have values in [A10] and [B9] then find with using xlByColumns will return you 9 row instead of 10. So xlByColumns is better to use when you trying to find the last column, not the row. – Mathieu Guindon Jan 16 '17 at 15:22 … So insert the following lines above the offending line: Counter = "1" Do Until Counter = MaxCounter + 1 ' Inner loop.

I have a piece of code that is looking at a selection of cells and using the find function to find a number in the selected cells. I have tested your code and it returned the message box as if the cell with the given value was not there. I have a simple macro which deletes all lines in a sheet containing a particular variable, "Hobbs" in the code below.

I created the macro by recording a 'find'. Intially the code gets the first and last number to look for from some reference cells.

Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. 'Set the find value Additionally, some scammers may try to identify themselves as a Microsoft MVP.

Also Sheets("MySheet").Range(Cells(672, 1), Cells(681, 1)) will blow up if Sheets("MySheet") isn't the active sheet, because Cells(xxx, yyy) calls are implicitly referring to the ActiveSheet, so you need to qualify these calls too; use With Worksheets("MySheet") then do Set rSearchRange = .Range(.Cells(672, 1), .Cells(681,1)) inside the With block (notice the dots).

We provide all the Latest Technology (Tech) News, How-To Tips, Guides, Products Reviews, Products Buying Guides & much more wise things.

If all cells in column F are blank, selectRange will be an empty range, and you cannot copy an empty range. Thanks a lot for the swift reply!

The issue I have is that on occassions a number might be missing in a sequecing it is looking for, i.e. Of course, your code will return correct number due to used Columns(1) range, but in case when … It then loops until all the numbers have been found and the data copied somewhere else. Often, a function or property can return an object reference - a common example is Excel's Range.Find method, which returns a Range object: Dim resultRow As Long resultRow = SomeSheet.Cells.Find("Something").Row However, I have tested it before, using a normal LOOKUP function and it matched those two cells perfectly! Sub DeleteSecretaries() MaxCounter = SecretariesLastRow ' Initialize variables.