0

I need some help dragging a string ("Decliners") down a column in excel. I know how to do this if I knew what cell was my starting point, but I first have to find the first blank row in my data set.
Once I've found my first blank row, I need to drag my string down from there in column C3.
This string is being dragged down just one column. I also don't know the range of this data set, given that it is dynamic.

Essentially I just need to recreate the action of double clicking the bottom right of the cell and the word "Decliners" fill to the bottom of the data set.

Code to select the first blank cell in worksheet:

Dim Pastesheet As Worksheet
Dim Decliners As String
Decliners = "Decliners"

Set Pastesheet = Worksheets("Ent Gainers_Decliners")
Pastesheet.Range("C3").End(xlDown).Offset(1, 0).Select
'Where I need the word "Decliners" dragged down from the cell selected

Spreadsheet picture

Davey
  • 123
  • 1
  • 3
  • 18
  • You may need to specify which column determines the length of the values for column C and whether column C already has some values that should not be disturbed. –  Jul 07 '16 at 19:54

1 Answers1

5
With Pastesheet
    .Range(.Range("C3").End(xlDown).Offset(1),.Cells(.Rows.Count,4).End(xlUp).Offset(,-1)).Value = Decliners
End With

This piece of code will set the value of the variable Decliners from the row after the last data set from Range C3 down until the last corresponding row of used data in column D for column C.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Good catch on locating the last row in column B; I missed that myself. –  Jul 07 '16 at 19:43
  • @Jeeped - I was about to write something to you, but you deleted it :) – Scott Holtzman Jul 07 '16 at 19:43
  • So I tried this and nothing happened. I replaced `end(xlUp)` with `end(xlDown)` and the entire column filled with "Decliners". Not really sure why it's not working. – Davey Jul 07 '16 at 19:50
  • I think you might have to filter the selection with SpecialCells(xlCellTypeBlanks). –  Jul 07 '16 at 19:51
  • @Davey - can you edit your question with a sample data structure or screenshot. Perhaps what I imagined your data set to look like is not what it looks like. – Scott Holtzman Jul 07 '16 at 19:54
  • Let me know if you are able to view that picture. The site is blocked on my computer. I need to drag "Decliners" in the blank cell ("C552") right after "gainers"...note it won't always start at that cell. – Davey Jul 07 '16 at 20:09
  • 1
    @Davey - much easier to help now :) See the edited answer. – Scott Holtzman Jul 07 '16 at 20:21
  • 1
    Worked like a charm! Thanks Scott! Really appreciate it – Davey Jul 07 '16 at 20:29