Free Microsoft Excel 2013 Quick Reference

Wrong result returned by UsedRange.Rows.Count

I have been told that a colleague is having problems with
UsedRange.Rows.Count returning a very large value that far exceeds the
actual number of rows in the worksheet. Since we are iterating through
the rows, although there are work-arounds we can use to identify the end
of the data, it is a nuisance that this property seems to be unreliable.

Has anyone else had a similar problem? Does anyone know of a cause? Is
there a more reliable way of identifying the number of used rows in a
worksheet, in order to iterate through them?

I think that the code is currently running on Windows XP against Excel
2003, although it was originally developed and tested on Windows 2000
against Excel 2000. The object variables are all late bound.

Regards,
--
Jeff


Hi,

I use this command to know what will be the next row to use to save th
data of the invoice I just printed. The data of every invoice is sav
on one line of the data sheet.

The problem is that if my DATA sheet is empty, no line used, th
command UsedRange.Rows.Count will return 1.

If one line is used, it will also return 1.

To go around this problem, I put a dummy in the first row. But I don'
like it.

Any idea on how to fix my problem ? Is there a way to hav
UsedRange.Rows.Count return 0 when no line are used?

Thanks!

Alai

--
Message posted from http://www.ExcelForum.com

Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as to
what might be going wrong?

Thanks in advance

Pete

I used Sheets(1).Cells(1, 1).Rows.End(xlUp).Count instead of UsedRange.Rows.Count in this code , but it didn't succed with me. Why and how to do that

Code:
Dim i As Long, j As Long
j = 1
For i = 1 To UsedRange.Rows.Count
Sheets(2).Cells(j, "a").Value = Sheets(1).Cells(i, "a").Value
Sheets(2).Cells(j, "b").Value = Sheets(1).Cells(i, "b").Value
Sheets(2).Cells(j, "c").Value = Sheets(1).Cells(i, "c").Value
j = j + 1
Next i
End Sub


Hi,

In VBA I'm setting a variable as follows:
NumRows = Sheets("Summary").UsedRange.Rows.Count

and NumRows is subsequently used to make sure a chart has the correct
last row after an update that changes the number of used rows in my
worksheet.

The problem is that my worksheet contains 80 rows of real data. However
there once existed 600 rows. Would be grateful if anyone knows how I
can get Excel to forget about what once existed and only concentrate on
cells that now have a value in them?

--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=398856

Hi

I'm using ActiveSheet.UsedRange.Rows.Count to identify the last row in a Pivot Table but when pivot table is updated and number of rows is less than the previous version it still counts the rows as if it's the old Pivot Table defining the area. Funny part is it only does it in the first sheet. All the following sheets is correctly updated. Any way to get around this?

Thanks,
Kjaer

Some details:

Old Pivot Table is 59 rows
New Pivot Table is 46 rows

I'm deleting the data not part of the Pivot Table using this code (PT has been updated at this point of time):

Range("I13:L65536").Select
Selection.Delete
Selection.Interior.ColorIndex = xlNone

Then I'm trying to identify the number of rows with this:

Dim myCount As Integer
myCount = ActiveSheet.UsedRange.Rows.Count

But when I copy the formulas I need and try to paste them next to the PT using this code:

Range("I12:L12").Select
Selection.Copy
Range("I13:L" & myCount).Select
ActiveSheet.Paste

it still assumes 59 rows

Hi,

In VBA I'm setting a variable as follows:
NumRows = Sheets("Summary").UsedRange.Rows.Count

and NumRows is subsequently used to make sure a chart has the correct last row after an update that changes the number of used rows in my worksheet.

The problem is that my worksheet contains 80 rows of real data. However there once existed 600 rows. Would be grateful if anyone knows how I can get Excel to forget about what once existed and only concentrate on cells that now have a value in them?

Hi,
I am using below statement to count the rows with data in a sheet in excel --- 'how many rows are used in the Target worksheet TrgusedRowsCount = TrgcurrentWorkSheet.UsedRange.Rows.Count

But if that sheet contains some blank rows in between ,then how can i exclude that number of blank rows and get the eaxct used rows count in the sheet.

Please help me on this.

Thnaks-Amar

Hi,

Is it possible to update the usedrange.rows.count propert
( i.e totalrows present )after a row deletion operation( without a subsequent save operation).Basically this i would require when i am in a process of running a set of macros where the first macro deletes 5 rows from the end and the second macro uses the usedrange.rows.count property to draw a border at the end of data present.In the above situation when a save is not done the second macro draw borders wrongly (since the row count is not updated).

Is there a way to update the row count(determined from ausedrange.rows.count),without the save
operation? This would basically very useful for running macros which act on the sheet based
1) usedrange.rows.count property
2) Doesn't know anything about the previous macro run.

With Regards
Gopal

I need to count the number of occurrances of a specific date range within a column. Column B contains the dates. The formula I am using is:

=SUMPRODUCT(--([SBIWeek1.xls]data!B2:B1000>=10/1/2007),([SBIWeek1.xls]data!B2:B1000

I am trying to create a function to count number of items in a column excluding row1 which has headers and save the last row as a reference so I can insert this into a formula.

So I have a basic formula which will be repeated for several different columns.

This will go something like this.
Range("B23").Select
	ActiveCell.FormulaR1C1 = "=SUM("Z2":("RowRef", "Z"))/ClassCount"
	Range("B23").Select
And this is where I am going with trying to create a function to insert the class count and row refernce.
Dim lastrow As Long, i As Long
	Dim RowRef = int
	ClassCount = int
	lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lastrow To 2 Step -1
    ActiveSheet.UsedRange.Rows.Count("B22").Paste
	ClassCount = ActiveSheet.UsedRange.Rows.Count
	RowRef = Not Sure	
	End Sub


I have made two Marcros 8 and 9 below. Neither completes the requirement of attaching the value of number of rows from a filtered sheet.

I attempted to use .UsedRange.Rows.Count and it returned total rows, not filtered rows.

To get around this in Marcro9 I put a =Countif(T:T,"K") in cell T2 of the spreadsheet. I then took the value from T2 and used it in my Macro. I don't like this option as it leaves my spreadsheet open to users moving rooms and the value stored in T2 being lost.

What is the VBA code to perform the Countif or Count.rows for filtered results?

Thank you for any help in advance.


	VB:
	
 Macro8() 
    Dim num_Rows As Integer 
    With Sheets("Data") 
        num_Rows = .UsedRange.Rows.Count 
    End With 
     
    Windows("SO CIM March 18 working.xls").Activate 
    Rows("5:5").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Rows("5:" & num_Rows).Select 
    ActiveSheet.Paste 
End Sub 
 
Sub Macro9() 
     
    Dim num_Rows As Integer 
     
    With Sheets("Data") 
        num_Rows = Range("T2").Value2 + 4 
    End With 
    Windows("SO CIM March 18 working.xls").Activate 
    Range("A5").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Selection.Copy 
    Range("A6:A" & num_Rows).Select 
    ActiveSheet.Paste 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear Experts,
The question is also posted on http://www.excelforum.com/excel-prog...ml#post2470496, http://www.vbaexpress.com/forum/show...710#post235710, but no feedback so far.

I have a UDF calculating averages by getting data from subordinate WBS and ignoring blank cells. For instance, if 1.1 is broken down into 1.1.1 (10), 1.1.2 (12) and 1.1.3 (25), the formula finds the corresponding values for the subordinates of 1.1 and calculates the average ((10+12+26)/3 = 16).
If, however, 2.2 is broken down into 2.1 (50) and 2.2 (blank cell), than the average should be 50.

When I debug my UDF on, say, cell I40 (5.5, which has 3 subordinates 5.5.1 (82%), 5.5.2 (23%), and 5.5.3 (blank cell, that have a subordinate 5.5.3.1 (85%)), I would expect to have an average of 63.(3)%.
This is exactly what I get in the Immediate Window (see below) when I get values from the cells.

	VB:
	
?rgeCriteria.Cells(i, 1).Value 
5.5.3 
5.5.2 
5.5.1 
 
?rgeCriteria.Cells(i, 1).Offset(0, 8).Value 
0.85 
0.23 
0.82 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The UDF, however, returns me something different. In part because, I guess, it treats 5.5.3 as blank and does not assume that it may have subordinates. It also does not seem to be drawing results from other cells with the same UDF.
Please help to fix the UDF. Your help will be very much appreciated. I am attaching the file for your convenience.

	VB:
	
 Range, _ 
    ByVal sCriteria As Range) As Variant 
     
    Application.Volatile (True) 
     
    Dim i As Integer 
    Dim sub_num As Integer 
    Dim blank As Integer 
    Dim sum As Variant 
    Dim count As Integer 
     
    For i = 1 To rgeCriteria.Rows.count 
        If rgeCriteria.Cells(i, 1).Value Like sCriteria.Value & ".#" Then 
            sub_num = sub_num + 1 
            sum = sum + rgeCriteria.Cells(i, 1).Offset(0, 8).Value 
             
            If IsEmpty(rgeCriteria.Cells(i, 3)) Then 
                blank = blank + 1 
            End If 
        End If 
    Next i 
     
    If sub_num = 0 Then 
        If IsEmpty(sCriteria.Offset(0, 2)) Then 
            AVGWBSNB = "" 
        Else 
            AVGWBSNB = sCriteria.Offset(0, 2).Value 
        End If 
    Else 
        AVGWBSNB = sum / (sub_num - blank) 
        Exit Function 
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


"Bernd P" > wrote in
Newsgroups: microsoft.public.excel.worksheet.functions
Subject: how can I use randbetween without repeating numbers in a set
Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT)
Message-ID:
>
>
>> See JE McGimpsey's site for a solution to this problem.
>> http://www.mcgimpsey.com/excel/udfs/randint.html
>
> A faster and more flexible RandInt you can find he
> http://sulprobil.com/html/randint.html

These array functions use Application.Caller to determine the size and shape
of the array result to be returned.

This poses a problem when they are executed from a macro using
Selection.Calculate under specific conditions, explained below.

At least, that is the case with my revision of Excel 2003 (11.5612.5606) and
VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn Ed.

However, I do not observe a similar problem with some Excel array functions,
e.g. LINEST[*].

So I am wondering: is there an alternative to Application.Caller that will
work under the specific conditions that exhibit the problem?

The problem might not always be apparent with Bernd's and McGimpsey's
RandInt() functions due to their random nature.

So for demonstration purposes, consider the array function myInts() below,
which returns an array of unique integers in a deterministic manner.

When it is invoked by an array formula in A1:B5, e.g. =myInts(), it returns
the integers 1 through 10.

However, the array formula __seems__ to misbehave, returning 1 in all 10
cells, when it is invoked by a macro that implements the paradigm described
below.

The operative word is "seems". In fact, the array function behaves
correctly. The root cause of the problem is an anomaly (defect?) of the
paradigm implemented in the testit() macro below.

The root cause of the problem seems to be related to setting
Application.Calculation=xlCalculationManual, executing Selection.Calculate,
then restoring Application.Calculation=xlCalculationAutomatic. The problem
manifests itself in the last step, setting
Application.Calculation=xlCalculationAutomatic.

This paradigm is common when measuring performance. For example, see
http://msdn.microsoft.com/en-us/library/aa730921.aspx, Sub RangeTimer() in
the section "Measuring Calculation Time".

I have instrumented the testit() macro and myInts() function to make the
root cause more apparent.

As the instrumentation demonstrates, the problem is: when we set
Application.Calculation=xlCalculationAutomatic after Selection.Calculate
performed a recalculation, myInts() is invoked again from each of the cells
in A1:A10 individually, not as an array.

Thus, Application.Caller is a single cell, not a range; and
Application.Caller.Rows.Count and Application.Caller.Columns.Count are 1
instead of the reflecting the shape of the array formula, as myInts()
expects.

Consequently, the array formula cannot return a set of unique integers.
Instead, it always returns the first integer in the set.

(But Selection.Calculate did invoke myInts() once with the array formula
range, and the function did return a set of unique integers. This is
evident by setting a breakpoint after Selection.Calculate.)

Similarly, the RandInt() functions in an array formula will not necessarily
return a set of unique integers when they are executed by the testit()
macro.

However, LINEST does not have such a problem when Selection is the array
formula described in the footnote below. After testit() is executed, V1:W5
will contain their expected values[*], not the value of V1 repeatedly as we
might expect given the analysis above.

So I suspect that LINEST is using something other than (just)
Application.Caller to determine the shape of the array formula that includes
Application.Caller, and it is returning the result in the array that
corresponds to the position of Application.Caller in the array formula.

How might that be done?

Alternatively, is there a way to implement the paradigm in the macro
testit() so as to avoid the anomaly when
Application.Calculation=xlCalculationAutomatic is restored?

-----
Endnotes

Private callcnt As Long

Sub testit()
Dim oldIter, oldCalc
Debug.Print "====="
callcnt = 0
With Application
oldIter = .Iteration
oldCalc = .Calculation
.Iteration = False
.Calculation = xlCalculationManual
On Error GoTo done
Selection.Calculate
done:
.Iteration = oldIter
.Calculation = oldCalc
MsgBox "callcnt = " & callcnt
End With
End Sub

Function myInts()
Dim rows As Long, cols As Long, n As Long, x() As Long
callcnt = callcnt + 1
With Application.Caller
rows = .rows.Count
cols = .Columns.Count
Debug.Print "myInts: callcnt="; callcnt; ", "; _
.Address; ", rows="; rows; ", cols="; cols
ReDim x(1 To rows, 1 To cols)
n = 1
For r = 1 To rows: For c = 1 To cols
x(r, c) = n: n = n + 1
Next c: Next r
myInts = x
End With
End Function

Function myZero()
Dim rows As Long, cols As Long
callcnt = callcnt + 1
With Application.Caller
rows = .rows.Count
cols = .Columns.Count
Debug.Print "myZero: callcnt="; callcnt; ", "; _
.Address; ", rows="; rows; ", cols="; cols
End With
End Function

[*] Actually, even LINEST might behave slight differently. Consider the
following simplistic example. X1:X4 contains 1 through 4, Y1 contains
=2*X1+5, which is copied down through Y4, and V1:W5 contains the array
formula =LINEST(Y1:Y4,X1:X4,TRUE,TRUE)+myzero(). When executed normally, V4
results in #NUM!. I assume that is correct under the circumstances. But
when executed using the testit() macro, V4 results in zero.

Hello,
I want to enable button on the Menu and Toolbar if User is selected entire row (only one row). If it is selected some Range or more than one Row, the button must be disabled. I'm using this to detect what is the user selection:

Public Function RowIsSelected(Optional wsheet As String) As Integer
    Dim ws As Worksheet
    Dim colmn As Integer
    Dim rws As Integer

    ' If no name is supplied - the default is the activesheet
    If wsheet = "" Then wsheet = ActiveSheet.Name

    Set ws = ActiveSheet
    Application.ScreenUpdating = False
    Worksheets(wsheet).Activate

    colmn = Selection.Columns.Count
    rws = Selection.Rows.Count


    ' Find if only one row is selected. If less than a row - return 0,
    ' if 1row - return 1, if more than one - return he number of rows.

    If colmn = ws.Columns.Count And rws = 1 Then
        RowIsSelected = 1
    ElseIf colmn < ws.Columns.Count Then
        RowIsSelected = 0
    Else
        RowIsSelected = rws
    End If

    ws.Activate
    Application.ScreenUpdating = True

End Function

I thought that everything is OK, I found that if the user selects entire row, and after that selects one more by holding Cntrl key pressed and selecting row, then I get wrong result, that only one row is selected. In fact I have two rows selected.

Any ideas how I can solve this issue? I know there was similar post recently, but I can not find it. Thanks.

Buran

I've used these before

ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count
These sometimes return the wrong values. Why?

Hi Team,

I've searched arround and could not find an answer to this that is clean. I would like a simple statement that would return all the elements of a specified row in a named range to a range of cells. The table below is named mytable1

A B C 1 Item Comment Purhase oder 2 A01 sdf 12 3 A02 sdfsafd 12 4 A01 sdfsdf 14 5 A03 sdfsd 15

The code I am trying (which is working) is as below

	VB:
	
 return_first_row() 
    Set table_range_row = Range("mytable1").rows(1) 
    Range("A8:C8") = table_range_row.Value2 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a better way of returning the entire row? I ask because you need to know the full dimension (no of columns) of the rows so you can specify the output range. i.e. to make the above code more flexible to cope with any table dimension it would have to be


	VB:
	
 
row_ouput =8 
col_output =1 
range(cells(row_output,col_output),cells(row_output , col_output + range("mytable1").rows(1).columns.count)) =
table_range_row.value2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The code I would really have liked would have been.


	VB:
	
row_output = 8 
col_output =1 
For Each row_element In range("mytable1").rows(1) 
     
    cells(row_output,col_output).value = row_element 
    col_output = col_output +1 
     
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is something like this possible?
Is it because .rows(1) is not a collection so there are no row_elements "objects" in that collection? I've had to result to using the .value2 property. Why is this available and not the .value property?

To kindly summarise the questions:
1. Is there a way to access each cell in .rows(1) with a for each statement?
2. Why does a .value2 property exist and not a .value property as well?

Thank you very much,

Andy

When I copy and paste all four columns manually by using "end home" to find the last row there is not a problem.

I had to modify code that copied formulas from row 3 of col F G H I, as a group, and pasting to last row, to copying and pasting one column at a time. Instances where there were more than 2200 rows would return runtime error "Selection Too Large" .
The following works but looks rather sloppy. Does anyone have any suggestions to make this portion more efficient? The number of rows can range from 20 to 8000. The spreadsheet is being used to generate random numbers in order to pull samples of work. The spreasheet is cleared and rows are deleted prior to each use.

original

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3:i3").Select 
Selection.Copy 
Range("f4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
modified to

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3").Select 
Selection.Copy 
Range("f4:f" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("g3").Select 
Selection.Copy 
Range("g4:g" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("h3").Select 
Selection.Copy 
Range("h4:h" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("i3").Select 
Selection.Copy 
Range("i4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, thank you for your help and, in this case, valuable input.

This post is similar to a recent post by Brent W, although I'm not sure it's exactly the same.

The example is a bit contrived, but it illustrates what I am trying to do: make a summation cell show only the sum of cells in a column for rows returned by an AutoFilter selection, independent of what AutoFilter specification is selected.

Row 1 Contains summation cells
Row 2 Contains column headers
Rows 3 to N contain data

Col A: Name of Metro Area
Col B: Population of Metro Area
Col C: Contains an "x" if Metro Area has a median income over $35,000
Col C: Contains a "y" if Metro Area Has a median income over $50,000
Col D: Contains an "x" if the Metro area is northern
Col D: Contains an "y" if the Metro Area is southern

Cell B1 = sum(B3.B65536)

Col A Col B Col C Col D
Row 3: Detroit 4,000,000 (null) x
Row 4: Atlanta 3,000,000 x y
Row 5: Dayton 300,000 y x
Row 6: Macon 100,000 x y

With AutoFilter on and:

Nothing filtered, I want cell B1 to show 7,400,000

Col C filtered for I want cell B1 to show
x 3,100,000
y 300,000
(Blanks) 3,000,000
(NonBlanks) 3,400,000

Col D filtered for I want cell B1 to show
x 4,300,000
y 3,000,000
(Blanks) 0
(NonBlanks) 7,400,000

Can this be done?
If so, can it be done within Excel, or does it require VBA?
Either way, how might it be done?

TIA,

Far Farley

Hey All,

I am a newbie here, but am not new to VBA. I am trying to complete a large application that I wrote using Excel and VBA for work, but I am stuck on one stupid little thing.

My problem is that I need to be able to determine the number of used rows in a WorkSheet (i.e. rows that contain at least one cell of data). Logically, I thought that the following line of code would get me this result:

	VB:
	
 
dblNumUsedRows = ActiveSheet.UsedRange.Rows.Count 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this code does not work for me. I have 406 rows containing data on the active sheet, but this code is returning dblNumUsedRows = 12605.

Weird!

Can anyone help me out? I would greatly appreciate it!

Thanks!
Bryan

Hi

I am trying to consolidate 23 small macro's that are as follows:

	VB:
	
 ReasonCode17() 
    Dim xrow As Long 
    xrow = 2 
    Dim ws As Worksheet 
    Dim lastrow As Long 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    Do Until xrow = lastrow + 1 
        ActiveSheet.Cells(xrow, 4).Select 
        If ActiveCell.Text = "ARTADJ" Then 
            Selection.EntireRow.Delete 
            xrow = xrow - 1 
        End If 
        xrow = xrow + 1 
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All that change's in most of them is the details in The If statement "*********", I had to create a macro to run all the little macro by using the application.run function but I am sure that all this can be made much more simplare then what is have put together.

This is what I have tried, made somebody can see what I have done wrong and guide me in the right way.

	VB:
	
 ReasonCode() 
    Dim xrow As Long 
    xrow = 2 
    Dim ws As Worksheet 
    Dim lastrow As Long 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    Do Until xrow = lastrow + 1 
        ActiveSheet.Cells(xrow, 7).Select 
    Else: ActiveCell.Text = "TONER RETURNS" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Damaged Stationery Stock" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Previous Customer Return" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "PEP Found" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "CORRECTION OF PRIOR STK ADJ" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Goods Physically Found" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Stk prd substitute by another" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Stock Transfer" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Obsolete Stock Stationery" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "WHOLESALER PRODUCT NOT FOUND" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Stationery Found" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Wholesaler Product Found/lost" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "TONER RETURNS" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "CORRECTION OF PRIOR STK ADJ" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Split Pack Unsellable" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Print Write Off" 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Damaged Print Stock" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    Else: ActiveCell.Text = "Stock Take Error" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
        ActiveSheet.Cells(xrow, 4).Select 
    Else: ActiveCell.Text = "ARTADJ" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
        ActiveSheet.Cells(xrow, 21).Select 
    Else: ActiveCell.Text = "N" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
        ActiveSheet.Cells(xrow, 21).Select 
    Else: ActiveCell.Text = "T" 
        Selection.EntireRow.Delete 
        xrow = xrow - 1 
        xrow = xrow + 1 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in Advance

MIG285

Hello,

I have a worksheet where a user will copy data into column A, starting in row 6.
This data may or may not have empty rows between the data. The data string will be comprised of numbers and letters (i.e. ab7sf63-sdg-fj89b/1.2.840.1.111.dts)
I need to remove the extra blank rows, and count the number of entries containing the string "dts" and count the number of entries NOT containing .dts.
The number of rows entered by the user varies.
This is the code I have:

	VB:
	
 DeleteEmptyRows() 
     
     'Find the last row containing data
    Dim LastCell As String 
    LastCell = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address 
     
     'Clear the previous results
    Range("C4:D4").Clear 
     
    LastRow = ActiveSheet.UsedRange.Row - 1 + _ 
    ActiveSheet.UsedRange.Rows.Count 
    Application.ScreenUpdating = False 
    For r = LastRow To 1 Step -1 
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete 
    Next r 
     
    Range("C4").Select 
    ActiveCell.FormulaR1C1 = "=Countif(A:A, ""*""&dts&""*"")" 
     
    Range("D4").Select 
    ActiveCell.Formula = "=Countif(a6:LastCell, "  "&" * "&c3&" * ")" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So far, my first formula entry posts the formula: " =COUNTIF(a : (a), "*"&dts&"*") "
This reference is obviously incorrect. I've changed my quotation marks around, but still cannot get the correct formula to post.
What am I missing?
Will the same apply to the next formula?

Thank you so much!

Hi there,

People on this board have helped me before, and I am wondering if I can have some help again? I have created a catalogue (with much help!!) in Excel that is searchable and returns results to a results page. This search sorts through all the data on each of the multiple worksheets - I would like to change this so the search only looks for results in Column A...

This is the code I already have - i'm not sure exactly what to alter to change this


	VB:
	
 KeywordSearch() 
     
    Dim UserEntry As String 
    Dim vItem 
    Dim SheetVals 
    Dim FoundSht As Worksheet 
    Dim NextRow As Long 
    Dim rOffset As Long 
    Dim cOffset As Long 
    Dim rowCt As Long 
    Dim colCt As Long 
    Dim rTest As Long 
    Dim cTest As Long 
    Dim n As Long 
    Dim CatchRow As Boolean 
    Dim CatchCell As Boolean 
    Dim CopyRow As Long 
     
     
    UserEntry = InputBox("Enter keywords, separated by commas") 
    UserEntry = Replace(UserEntry, " ", "") 
    vItem = Split(UserEntry, ",") 
    If UserEntry = "" Then Exit Sub 
    Application.ScreenUpdating = False 
     
    Set FoundSht = Sheets("Results") 
    NextRow = 3 
    Set rng = FoundSht.UsedRange 
    Set rng = rng.Offset(2) 
    rng.ClearContents 
     
     
    For Each Sheet In ThisWorkbook.Sheets 
        If Sheet.Name  FoundSht.Name Then 
            rOffset = Sheet.UsedRange.Row - 1 
            cOffset = Sheet.UsedRange.Column - 1 
            rowCt = Sheet.UsedRange.Rows.Count 
            colCt = Sheet.UsedRange.Columns.Count 
            SheetVals = Sheet.UsedRange 
            For rTest = 1 To rowCt 
                CatchRow = False 
                For cTest = 1 To colCt 
                    CatchCell = True 
                    For n = LBound(vItem) To UBound(vItem) 
                        If InStr(1, SheetVals(rTest, cTest), vItem(n)) = 0 Then 
                            CatchCell = False 
                            Exit For 
                        End If 
                    Next n 
                    If CatchCell = True Then 
                        CatchRow = True 
                        Exit For 
                    End If 
                Next cTest 
                If CatchRow = True Then 
                    CopyRow = rTest + rOffset 
                    Sheet.Range(CopyRow & ":" & CopyRow).Copy _ 
                    FoundSht.Range("a" & NextRow) 
                    NextRow = NextRow + 1 
                    FoundSht.Activate 
                     
                End If 
            Next rTest 
        End If 
    Next Sheet 
     
    If CopyRow = False Then Goto Err_Entry 
     
    Exit Sub 
     
Err_Entry: 
    MsgBox "No Results Found" 
    ActiveWorkbook.Save 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be much appreciated!

Hi,

Thank you for taking the time on new years day to look at my problem!

Ive searched this forum and MSDN and cant find an answer - I must be trying to use arrays in the wrong way. I simply want to create an array from values in a column and perform calculations on each element and store the results for each element. I therefor need a 2d array. I also dont know the number of values in the column and therefor dont know the number of elements that will be in the array. How would I declare this array. Ive tried

	VB:
	
 2) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
, but that returns an error - 'Constant expression required'.

Any help would be appreciated!

I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores.

I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck...

Thanks,
David


	VB:
	
 Hide_Scores() 
    Range("B15:EU35").Select 
    ActiveSheet.Unprotect 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlCenter 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    With Selection.Font 
        .Name = "Arial" 
        .FontStyle = "Bold" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = 2 
    End With 
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    x = ActiveSheet.UsedRange.Rows.Count 
    ActiveCell.SpecialCells(xlLastCell).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines