Copying a formula to x # of rows for one column 3

[FrodoBaggins]

I am automating a text file import to excel process, and the number or records is different each time the process is run. I need to insert a column and copy a fixed label for each row. e.g. for rows A1:A200 the word "Hello" is needed.
The problem I have is how do I determine the number of rows and get the formula to recognise that I only want the word Hello to appear in column A.
the format is something like:

A B C D
Hello 25 45 36
Hello 24 41 36
etc..

 

[Stretchwickster]

Assuming you've already populated a sheet with the data, why not run a loop until you find the first blank cell in a certain column. Then when that cell is located - store its address. You can then select the range (i.e. Range("A1:A" & cellAddress)) and use the AutoFill method to fill them with "Hello". Clive

 

[Loomah]

Hi
This should get you moving

Sub bus()
Dim lRow As Integer
' There are many ways of finding your last row
' this is one of them!
lRow = ActiveSheet.UsedRange.End(xlDown).Row
Range(Cells(1, 1), Cells(lRow, 1)).Value = "Hello"
End Sub

Or you could just put the ring on and sneak out!

What did You do on Your Birthday!!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Stretchwickster]

Loomah,

Is there a way to specify the start position of the UsedRange property? I have a sheet that has 4 header lines then a blank row before the data starts, the data then a blank row, then a different lot of data, then a blank row etc. So I need to be able to say: what's the row number for the third blank row (i.e. after the header and 2 sets of data)? Clive

 

[Loomah]

A strange one!
Fistly I'd suggest reconsidering the way you store your data. Blank lines aren't the most helpful thing in the world (if I remember correctly) when manipulating data.

Secondly, UsedRange is a bitch. End of story. I use it for quick methods such as this one and on the assumption that imported data will create a clean used range and will be reliable in the case here.

Finally your problem!
To answer the question directly

ActiveSheet.UsedRange.Cells(1, 1).Select

will select the first cell of the used range.

Looking further I'm a little confused. If you're looking for the blank line at the end of all your data then...

[a65536].End(xlup).offset(1,0).Row

will return the row number

If you are looking to find the blank row at the beginning of your last block of data than I think this will do the trick

Sub s()
Dim c
With ActiveSheet.Range(Cells(1, 1), Cells(65536, 1).End(xlUp))
    Set c = .Find(what:="", searchdirection:=xlPrevious)
End With
c.Select
End Sub

all of the above assumes data in column A!

Happy Daze
;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[FrodoBaggins]

Thanks for the comments, this is useful when Column A has data in it. When column A is blank it does not work, any solutions to this?

 

[Loomah]

It's my birthday and I think I'm going senile!
I'm confused again, Frodo, you're request suggests that column A should be empty and filled with a label - hence my suggested solution.

If referring to Stretchwickster's questions, do it on column B! That means changing A65536 to B65536 and cells(1,1) to Cells(1,2) etc....... Could always use C,D,E,F or IV if appropriate!!

If you are looking for the last Row (or column or first row/column) there are two, dare I say, excellent FAQs in this forum suggesting different ways of doing this.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[LoNeRaVeR]

FrodoBaggins:

You can normally find the last row of data by using

  iRow = Cells(1, 1).SpecialCells(xlLastCell).Row

This works with most text files. However, some Excel files where data was previously deleted may not provide an accurate LastCell since it looks for the last edited cell. A foolproof way to accomplish this would be with the following code.

Sub FindLastRow()
  Dim iCol     As Integer
  Dim iLastCol As Integer
  Dim iLastRow As Integer
  iLastRow = 1
  iLastCol = Cells(1, 1).SpecialCells(xlLastCell).Column
  For iCol = 1 To iLastCol
    If Cells(65536, iCol).End(xlUp).Row > iLastRow Then _
      iLastRow = Cells(65536, iCol).End(xlUp).Row
  Next iCol
End Sub

This searches for the last value in each column and resets the the variable iLastRow to the last row. Use this variable when filling the data in the inserted column.

Regards, LoNeRaVeR

 

[acron]

Something like

Sub FillColumnA()
    Range([A1], [B1].End(xlDown).Offset(0, -1))="Hello"
End Sub

That should enter "Hello" in column A, until the first blank cell in column B is reached.

If you have a header row, change A1 to A2

 

[FrodoBaggins]

Acron has come up with a solution that solves the problem perfectly, so a big thank you for that.

Thanks also to everyone else that offered up a solution.

Regards

Frodo

 

[FrodoBaggins]

I have a slight variation on this problem.

If I want a formula to be entered into column A instead of "Hello" how do I do this?
e.g. for Cell A2 =Sum(B2:E2)
Cell A3 =Sum(C2:E3)
etc.

 

[RobBroekhuis]

Sub FillColumnA()
Range([A2], [B2].End(xlDown).Offset(0, -1)).formulaR1C1="=sum(RC[1]:RC[4])"
End Sub

Rob

 

[FrodoBaggins]

Thanks RobBroekhuis, simple when you know how.
I purchased a Microsoft book called Step by Step VBA for Excel, but it does not seem to provide useful practical tips.
Can you recommend any useful books, I cannot rely on you guys helping me out every time.

Frodo

 

[RobBroekhuis]

First book I used was "Excel 2000 Programming for Dummies". Rudimentary, but got me started real quick. I also read John Walkenbach's book (don't remember the title), which is more in-depth, and lots heavier ;-)
Rob