Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

ActiveSheet.UsedRange.Rows.Count

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


Post your answer or comment

comments powered by Disqus
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 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

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


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

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

This code will count the un-hidden rows, were both column A & B are true:
Column A = "Bill and Column B = "Y"
where other data is also in the range.

Code:
 
Sub SumVisible()
Dim Rng As Range
Dim c As Range
Dim lngR&, lngCnt&
 
lngR = ActiveSheet.UsedRange.Rows.Count
 
Set Rng = Range("A1:A" & lngR).SpecialCells(xlCellTypeVisible)
 
On Error Resume Next
 
For Each c In Rng
If (c.Value = "Bill" And c.Offset(0, 1).Value = "Y") Then lngCnt = lngCnt + 1
Next c
 
MsgBox lngCnt
End Sub
I need a sheet formula to do the same and am having a problem doing it, tried SubTotal 103 and Sumproduct, but keep getting a blank answer, played with these formulas so much I do not have a starting point to post.

I was using this code

RwsCnt = ActiveSheet.UsedRange.Rows.Count
for i = 1 to RwsCnt

'Code

Next i

it works correctly but the value of the row count increases as the
macro goes along

how can I get this to count just the rows in column B?

Hi, all!
There is an option in Excel to count all used rows in active sheet:
ActiveSheet.UsedRange.Rows.Count

I would like to count rows in one column, that is shorter then other.
How do I specify certain column to count? Something like:

Columns("M:M").UsedRange.Rows.Count

Reg. VK

I have been asked to display a count of the rows in each sheet of an Excel
file at the top of each sheet (so that the user does not have to scroll down
to see how many rows of data there are). I want to do this through code.

Is this the best way to get a count of the rows:
ActiveSheet.UsedRange.Rows.Count
And I probably need to subtract 1 for the row with headings.

I also need to know--is there a way to display the count of rows above row 1
(where the column headings are)? Is there a row zero?

Thank you for any help you can give me,
Judy

Hello,

I am trying to count all the records in the active worksheet and exporting it to the database(sql.).

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
In 'lastRow' i should be getting total number of records in the worksheet,but i m getting only half.
Could anyone suggest what i should be using instead?

I've used these before

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

Hi all ,
I am not that good in VBA. but i can use your help to be a GURU.

I asked the same questions in mrexcel forum , but i didn't get any help.
http://www.mrexcel.com/forum/showthr...next-empty-row

I have a UserForm which contains 4 textBox and a button named as follow:

(the textBoxes : mytxt, mytxt2, mytxt3, mytxt4) and (the button : cmdAdd)

on the spreadsheet , i have the following
Range "name1" is ("A:A")
Range "name2" is ("B:B")
Range "name3" is ("C:C")
Range "name4" is ("D:D")

What i want to do is to use the UserForm to add students names to the columns A,B,C and D.so far so good.

The Problems are :
1- i want to add the data to the ranges in the spreadsheet. i wrote some codes to add the data to the first empty row. the first data (mytxt.Text) was added a row above the rest. but this happens in the first time i add data.After that the data was add at the same row.

2- i want to check if the whole name (mytxt.text And mytxt2 And mytxt3 And mytxt4) -which will be in a row- already exist or not. if not add it to the first empty row. if it exists, notify me.

here is my codes


	VB:
	
 cmdAdd_Click() 
     
     
    Dim FirstName As String 
    Dim FName As String 
    Dim GName As String 
    Dim FamName As String 
     
    Dim Rng As Range 
    Dim Rng2 As Range 
    Dim Rng3 As Range 
    Dim Rng4 As Range 
    Dim FirstEmpRow As Long 
     
    FirstName = mytxt.Text 
    FName = mytxt2.Text 
    GName = mytxt3.Text 
    FamName = mytxt4.Text 
     
     
    FirstEmpRow = ActiveSheet.UsedRange.Rows.Count + 1 
     
    If Trim(FirstName)  "" Then 
        With Sheets("Sheet1").Range("name1") 
            Set Rng = .Find(What:=FirstName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng, True 
            Else 
                ActiveSheet.Range("name1").Cells(FirstEmpRow, 1).Value = FirstName 
                mytxt.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
    If Trim(FName)  "" Then 
        With Sheets("Sheet1").Range("name2") 
            Set Rng2 = .Find(What:=FName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng2 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng2, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 2).Value = FName 
                mytxt2.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
    If Trim(GName)  "" Then 
        With Sheets("Sheet1").Range("name3") 
            Set Rng3 = .Find(What:=GName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng3 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng3, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 3).Value = GName 
                mytxt3.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
     
    If Trim(FamName)  "" Then 
        With Sheets("Sheet1").Range("name4") 
            Set Rng3 = .Find(What:=FamName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng4 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng4, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 4).Value = FamName 
                mytxt4.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     'Debug.Print FirstEmpRow
End Sub 

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


Hello Everyone,

I have an excel workook with alot of entries. The entries consist of items with several relative columns and rows associated with it.

Below is a snippet of the items and their relative data. What I want to do using VBA is search through all the values in columnA on sheet1 and for each value that is duplicate, write the single value onto sheet2.

Next I want to write the relevant RM and RM Qty next to the value on sheet2. From their I want to find the corresponding BOX associated with the item and write it and it's RM QTY after the other RM items.

Below this first example is the result I would like to see. I found a code that semi works, but it does this on the active sheet using activeCell. I want it to write on another sheet.
ITEM RM RM QTY A D8500WHI-WHI 900 A BOX0111 0.25 B D8500WHI-WHI 822 B BOX0111 0.25 C D8500WHI-WHI 546 C BOX0111 0.25
This is how I want it to look on another sheet.
ITEM FILL WEIGHT BOX CASEPACK A D8500WHI-WHI 900 BOX0111 0.25 B D8500WHI-WHI 822 BOX0111 0.25 C D8500WHI-WHI 546 BOX0111 0.25
Here is the code I found:

	VB:
	
 Macro1() 
    Application.ScreenUpdating = False 
     
    Dim ColumnsCount As Integer 
    ColumnsCount = ActiveSheet.UsedRange.Columns.Count 
    ActiveSheet.UsedRange.Activate 
    Do While ActiveCell.Row

Hi Everyone!

I have a macro that I am using and it works perfectly but I just want to tweak a little bit but I don't know how. This macro will take you back to another sheet from active sheet to the last record row but the cursor always ends up at column D. I would like to have the cursor location to end up at the last record row of column B instead.

Here is my macro code:


	VB:
	
 CPListUpdate() 
    Worksheets("CPList").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 3).Value = _ 
    Array(Cells(24, 1).Value, Cells(24, 7).Value, Cells(13, 4).Value) 
     
    Range("D1").Select 
    Sheets("CPList").Select 
     
    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
Again, the cursor always ends up at column D of the last record. I would like to have the cursor at column B instead.

I would appreciate everyone's help on this and thank you in advance.

RNF

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.

Hi Guys,

I have a problem with deleting rows that contains invalid formulas, these formulas are generated randomly daily in rows that contain no data and refer to empty cells in the same row.

Is there a code to delete rows an empty row in that by data it is empty, but not so because there is a formula?

currently this code of mine doesnt work on the zero data but formular-ed cell.. cause counta on the cell gives me 1


	VB:
	
 
EmptyR = ActiveSheet.UsedRange.Rows.Count 
For i = EmptyR To 1 Step -1 
    If Application.CountA(Cells(i, 1).EntireRow) = 0 Then 
        Cells(i, 1).EntireRow.Delete 
    End If 
Next i 

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

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

I am currently using this macro to remove all blank rows from my worksheet. Since I am already finding the LastRow, is there a way I can modify this to also delete the last 2 rows that contain information in column B?


	VB:
	
Dim LastRow As Long, r As Long 
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 
For r = LastRow To 1 Step -1 
    If IsEmpty(Cells(r, 1)) And IsEmpty(Cells(r, 2)) Then Rows(r).Delete 
Next r 

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

Hi all,

I am using the code below to delete rows which contain, in this example, SEOP. Where it says Value "SEOP", what can I use so it deletes values NOT CONTAINING SEOP? The text in a cell may say SEOP Germany so it needs to look for which have SEOP in the text.

Note the part of the below code where it says VALUE SEOP,

What can i put inplace of to make it look for cells containing??

Thanks,

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
l

ElseIf .Cells(Lrow, "B").Value "SEOP" Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

Hi there, I am needing to compare 2 columns from Sheet1 with 2 columns on Sheet2 and display a message when both columns match. I have this working somewhat, but my problem is that I have multiple instances of the data that's in the columns. So, if column A matches anything on Sheet2 and column H matches anything I get a match. I need to only show a match when the two columns match "together". I need this in a macro that automatically runs upon change event instead of formula. It will be used in a template by several beginner level Excel users.

I'm currently using named ranges "manu" and "subp" from Sheet2 for the match.

Here's my sample code for the change event:


	VB:
	
 Range) 
    Set rTarget = Target 
    Dim totalrows As Long 
    totalrows = ActiveSheet.UsedRange.Rows.Count 
    hrow = "$H$2:$H$" & totalrows 
     
     
    If Not Intersect(Target, Range(hrow)) Is Nothing Then 
        On Error Resume Next 
         
        Application.EnableEvents = False 
        Run "Exemptions" 
         'Turn events back on
        Application.EnableEvents = True 
         'Allow run time errors again
        On Error Goto 0 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here's the code for the macro itself:


	VB:
	
 Range 
 
Sub Exemptions() 
    Dim c As Range 
     
    For Each c In rTarget 
         
        If Not Range("manu").Find(c.Offset(0, -7).Value, LookAt:=xlWhole) Is Nothing Then 
            If Not Range("subp").Find(c.Value, LookAt:=xlWhole) Is Nothing Then 
                 
                MsgBox "Check for Possible Lead Exemption" 
                 
                 
                 
            End If 
        End If 
    Next c 
     
End Sub 

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

Hi There,

I am very confused about my macro I wrote which has data similar like this:

Number/ Name/ Amount1/ Amount2/ Amount3

100 XAOO 10 5 1
101 XAOB 20 10 2
100 XAOO 30 15 3
102 XAOC 40 20 4
102 XAOC 50 30 5

and it should be like this at the end:

Number Name Amount1 Amount2 Amount3

100 XAOO 10 20 4
101 XAOB 20 10 2
102 XAOC 90 50 9

So I thought I was done with this macro because it is working until I realised it doesnt add the last 3 rows to the consolidated list.

My original data comes from differentsheets and being organised in a seperate temporary sheet which is called "Notinuse3". So this macro runs with this tempo sheet. I run the macro with different data and the last try showed me while it is clearing content (which are the duplicated rows) it always does not iclude the last couple of rows. Here is my code, please check this out.


	VB:
	
 
Dim rInput, lastrange As Range 
Dim oDic As Object 
Dim nTotal(), vInput() 
Dim i2 As Long, j, k, lastrownumber As Long 
 
With Application 
    .ScreenUpdating = False 
    Sheets("NotINuse3").Activate 
    Set rInput = Range("A1", Range("E" & Rows.Count).End(xlUp)) 
    vInput = rInput.Value 
    Redim nTotal(1 To UBound(vInput, 1), 1 To 5) 
    Set oDic = CreateObject("Scripting.Dictionary") 
    With oDic 
        For i2 = 1 To UBound(vInput, 1) 
            If Not .exists(vInput(i2, 1)) Then 
                j = j + 1 
                For k = 1 To 5 
                    nTotal(j, k) = vInput(i2, k) 
                Next k 
                .Add vInput(i2, 1), j 
            ElseIf .exists(vInput(i2, 1)) Then 
                For k = 3 To 5 'Starting from K=3 because we don't want to add up first 2 rows
                    nTotal(.Item(vInput(i2, 1)), k) = nTotal(.Item(vInput(i2, 1)), k) + vInput(i2, k) 
                Next k 
            End If 
        Next i2 
    End With 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    Set lastrange = Range("A2:E" & lastrownumber) 
    lastrange.Clear 
     ''''''''''''Columns("A:E").Clear   (this one also does the same)
    Range("A2").Resize(j, 5).Value = nTotal 
    Application.ScreenUpdating = True 
End With 

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


Thank you so much for all the help !


No luck finding an answer? You could always try Google.