Count Rows

[Djblois]

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:

[VBA]
For I = 1 to 65556
[/VBA]

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]

[vba]

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row

[/vba]

[Djblois]

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]

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

[vba]
Option Explicit

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

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

[Djblois]

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]

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

Sorry, 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]

I think he wants to set range =

[Djblois]

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]

I found some code in a book but I can't get it to work

[VBA]FinalRow = Range("A65536").End(x1up).Row[/VBA]

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]

Can someone please help me?

[lucas]

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.
[VBA]
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(C22)"

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
[/VBA]

[Djblois]

I found some code in a book but I can't get it to work

[vba]FinalRow = Range("A65536").End(x1up).Row[/vba]

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]

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]

I thought there would be a way to say enter formula only in fields E2 up to the Last row counted.

[lucas]

you wish to drag up???? or you wish to enter a different formula in each cell of E2 up?

[lucas]

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]

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:


[VBA]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[/VBA]

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]

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]

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]

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