PDA

View Full Version : Count Rows



Djblois
06-12-2006, 10:46 AM
One last thing everyone, I added the for loop in but since the file can sometimes be very small and sometimes it can be very large I put in:


For I = 1 to 65556


This way it always looks at the whole file. I would like to speed it up since I am using that code in a few times. I know it is possible to count the amount of rows and then use that number to replace 65556 but I don't know how. Sorry, if I am being a pest but I am a beginner at VBA and I have 2 books on it that are good but they are both missing alot. If anyone can recommend an excellent book that has a lot of VBA code in it that would help also.

Daniel

Bob Phillips
06-12-2006, 10:59 AM
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row

Djblois
06-19-2006, 09:01 AM
Ok after I count the rows I would like to use it to specify the range from the 1st cell to the last one it counted. How can I do this?

Daniel

johnske
06-19-2006, 02:31 PM
Ok after I count the rows I would like to use it to specify the range from the 1st cell to the last one it counted. How can I do this?

Daniel
Option Explicit

Sub A()
Range(Cells(1, "A").End(xlDown), Cells(Rows.Count, "A").End(xlUp)).Select
End Sub
OR
Option Explicit

Sub B()
Range(Range("A1").End(xlDown), Range("A" & Rows.Count).End(xlUp)).Select
End Sub

Djblois
06-20-2006, 07:36 AM
John thank you but I would like to count it once and you use the count multiple times on different columns. Can you please help with that?
Thank you,
Daniel

johnske
06-20-2006, 08:10 AM
Ok after I count the rows I would like to use it to specify the range from the 1st cell to the last one it counted. How can I do this?

DanielSorry, I was answering the question above, where I took 'specify' to mean 'refer to' or 'reference'...

What I gave was two options that refer to ranges within a single column by placing two different comma-separated ranges within the range object. The examples given then select all ranges between those two ranges {note that there is an assumption that there may be empty rows at the top of the sheet - if there are not, erase .End(xlDown)}.

I'm still not sure what you want, if you want to actually count the number of rows, replace the .Select with .Rows.Count - alternatively, if you want to obtain the address of the cells replace .Select with .Address

lucas
06-20-2006, 08:24 AM
I think he wants to set range =

Djblois
06-20-2006, 12:13 PM
Sorry if I didn't explain it too well. I want to count the rows then I enter a formula in d1 and I want to copy it from row 1 to the last row counted and then I want to do the same for the f column and so on.

Djblois
06-22-2006, 08:09 AM
I found some code in a book but I can't get it to work

FinalRow = Range("A65536").End(x1up).Row

I want to use that to define the last row of data and then enter a formula in Cells d2 all the way down to the final row. Can someone help me get that code working and then help me use it to enter formulas.

Djblois
06-22-2006, 01:13 PM
Can someone please help me?

lucas
06-22-2006, 03:45 PM
Ok Daniel I finally got a minute to look at this and I have a working solution I think.....probably could be improved on by better coders but take a look: example attached.

Option Explicit
Option Compare Text
Sub DeleteRows()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Dim FillRange As Range
Range("D1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text <> ""
Range("E1").Select
ActiveCell.Formula = "=SUM(C2:D2)"

Range("D1").Select
Set FillRange = ActiveCell
On Error GoTo 0
If FillRange Is Nothing Then
Exit Sub
End If
Set FillRange = FillRange(1, 2)
lastrow = Cells(65536, ActiveCell.Column).End(xlUp).Row
If lastrow > FillRange.Row Then
FillRange.AutoFill Range(FillRange.Address & ":" & _
Cells(lastrow, FillRange.Column).Address), xlFillDefault
End If
Next
End Sub

Djblois
06-26-2006, 06:27 AM
I found some code in a book but I can't get it to work

FinalRow = Range("A65536").End(x1up).Row

I want to use that to define the last row of data and then enter a formula in Cells d2 all the way down to the final row. Can someone help me get that code working and then help me use it to enter formulas.

Can someone help me get this code to work and then be able to use it in formulas.

lucas
06-26-2006, 08:19 AM
So...you wish to enter a specific formula in the cells in the E column? The file in post 11 selects E1 and drags the formula down....can be easily reconfigured to start in E2....I'm confused as to your needs I guess.

Djblois
06-26-2006, 11:33 AM
I thought there would be a way to say enter formula only in fields E2 up to the Last row counted.

lucas
06-26-2006, 11:40 AM
you wish to drag up???? or you wish to enter a different formula in each cell of E2 up?

lucas
06-26-2006, 11:42 AM
or do you mean from the bottom up to the last row of D.....???? gotta have details....please try to communicate exactly what you are trying to do or I'm lost in stupid parenthesis....

Djblois
06-26-2006, 12:58 PM
or do you mean from the bottom up to the last row of D.....???? gotta have details....please try to communicate exactly what you are trying to do or I'm lost in stupid parenthesis....

Lucas,

I am sorry if I am not explaining it well enough. Here is what I am doing now:


Range("C1").Select
ActiveCell.FormulaR1C1 = "=PROPER(Trim(MID(RC[-2],16,50)))"
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

I perform the same set of code a few times in the Macro on different columns. I thought to speed up the Macro I can tell it to enter that formula in C1 all the way down to the last row counted. I feel this would be faster. Would it be?

lucas
06-26-2006, 01:26 PM
Please download the file from post 11
It does what your describing. You may have to change the column callouts but that should be it.

lucas
06-26-2006, 01:33 PM
If you can give me a step by step explaination of what your code in post 17 is supposed to do for you I think I can help you. Here's what I see.

Select cell C1
insert a formula into cell c1
then it looks like your trying to select the used range in column A to paste the formula that is in cell C1 (without dragging down...just the same formula in each cell of the used range in column A..) please clarify for me.

Djblois
06-26-2006, 01:49 PM
No problem Lucas,

1)I enter a formula in C1
2)Then I move back to A1 (Cause I know there are no blank lines in column A1)
3)Then I go to the bottom of A1
4)Then I move back over to the last row of the C column
5)Then I select from the last row of the C column up to C1
6)Then I paste the formula with relative references
7)Then I copy
8)Then I paste special-Values

Thank you Lucas for all your help

austenr
06-26-2006, 03:01 PM
How do you go to the bottom of A1?:bug:

lucas
06-26-2006, 07:41 PM
Try to tell me in plain language what you are trying to do, not what your steps are in your code as it is not working for me.

No problem Lucas,

1)I enter a formula in C1
why are you entering this formula now if your going to paste it in at the end?
2)Then I move back to A1 (Cause I know there are no blank lines in column A1)
we can check for the last used row of any column without moving there
3)Then I go to the bottom of A1
4)Then I move back over to the last row of the C column
no need to move
5)Then I select from the last row of the C column up to C1
6)Then I paste the formula with relative references
7)Then I copy
copy what and paste special to where?
8)Then I paste special-Values

Thank you Lucas for all your help
Take just a minute with your post and try to explain exactly what you want to see happen. This is what it looks like to me:

you want to insert an exact formula in all the cells of column C that have text in the A column of thier respective row...then I get lost on step 7 & 8

Djblois
06-27-2006, 06:12 AM
I want to enter a v-look up formula in the whold c-column from the top all the way down to the last cell. Right now this is the only way I know how to do it. Also, the reason why I copy the whole c-column and then paste them as values is I don't want the formulas anymore, I want it to be text.

lucas
06-27-2006, 06:20 AM
what is the vlookup formula?

Djblois
06-27-2006, 09:08 AM
It is not always a vlook up formula, a few spots I would like to enter formulas in the whole column without the added steps I am doing now. Here is the V-Look up formula:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Atalanta Codes.xls]Whses'!C1:C8,2,FALSE)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

I do this in a few spots. I want to enter the formula for everyline in column "?" from the first column down to the last one counted. I already count the lines in my formula, Now I want to use that count so the macro know which is the last row to get the formula.

lucas
06-27-2006, 09:34 AM
try this to remove the formula's and just leave the values:

With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Range("A1").Select

Djblois
06-27-2006, 10:59 AM
Thank you lucas. The code I pasted works no problem but I want to speed it up because I want to do alot with this macro and it is getting slower.

austenr
06-27-2006, 01:36 PM
Who would have thought that something so simple could be so complex???

mdmackillop
06-28-2006, 12:04 AM
Try

Sub DoLookup()
Dim Lst As Range
Application.ScreenUpdating = False
Set Lst = Range(ActiveCell.Offset(0, -1), _
ActiveCell.Offset(0, -1).End(xlDown))
Lst.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Atalanta Codes.xls]Whses'!C1:C8,2,FALSE)"
Lst.Offset(, 1).Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues
ActiveCell.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

mdmackillop
06-28-2006, 12:23 AM
try this to remove the formula's and just leave the values:

With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Range("A1").Select


Hi Steve,
This would convert all formulae to values, which may not be desirable.
Regards
MD

lucas
06-28-2006, 07:09 AM
True Malcolm. Thats how I use it mostly. I remove all formula and code before forwarding to some clients. I was trying to show Daniel how he could remove the formula's with less selecting, etc. Thanks for jumping in.