Filling empty cells with the above links

[Adiga Spirit]

Hi there,
I"m trying to fill empty cells with the above values including the links(hyperlinks).
to do this a tried-
1. Selecting the range.
2. Choosing empty cells using "special" in move to (CTRL + G).
3. Press "=" then upper arrow and ctrl enter.
Doing so fills the values without the links (hyperlinks).
Is there a solution to copy the links as well?

Thanks in advance

 

[Tom Urtis]

Well, the below macro could be better if we knew more about your situation than you are saying, example, what column are the hyperlinks in and what row do they start on (I assumed column A and row 1).

Also, is there a case where two successive cells have different hyperlinks in them (I asumed not based on the tone of your post).

Also, the fill will have to stop somewhere and as it is, it will stop at the last hyperlink or value in column A. This could look to you like the macro is incomplete because it did not fill the last hyperlink (if indeed the last value is a hyperlink) down to what you think the last row should be regarded as, based probably on the last filled row in some other column, which you did not specify either.

So this is for starters, which does work, pending further information from you about what you are really working with, and where.

Sub FillHyperlinks()
Application.ScreenUpdating = False
Dim LastRow&, xRow&, strLink$
'Assumes your hyperlinks are in column A starting in row 1
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
xRow = 2
strLink = Range("A1").Address
Do
If IsEmpty(Cells(xRow, 1)) = True Then
Range(strLink).Copy Cells(xRow, 1)
xRow = xRow + 1
Else
strLink = Cells(xRow, 1).Address
xRow = xRow + 1
End If
Loop While xRow < LastRow
Application.ScreenUpdating = True
End Sub

 

[Adiga Spirit]

first, thanks for your prompt reply and pardon me for mine. it's a beautiful day so I took my horse for a ride .

Regarding your first question it can begin in any column or row. the condition is that they all be in one column. I want to start with selecting the range manualy due to the fact that the number of the last empty rows vary from one case to another. Sometimes the following column defines the aforementioned number of empty cells and sometimes the second following column does that.
There could be a case where two successive cells have different hyperlinks. and in such a case, a hyperlink that has no following empty cell sholdn't be copied.

hope I've cleared the fog.

thanks again

 

[Tom Urtis]

OK, if you want to do that then the below macro will work, but there need to be a few rules applied in order to keep things running properly.

You don't need to remember these rules because the macro will enforce them if they are violated, and you'll be advised with a friendly message box.

First, the first cell of the selection needs to contain a value, that is, not be empty because if it was, there woud be nothing to copy to it.

Second, only one column at a time can be selected because you are filling.

Third, the selection must contain at least one empty cell, otherwise there would be no place to copy anything to.

Again, just go ahead and select whatever range you want to select and if you don't select the proper range based on the above guidelines, you'll be found out.

One rule not being programatically enforced is that every value be a hyperlink. I figure as long as you want to manually select the range, you are seeing for yourself what you want to fill and are thinking it's OK, be it hyperlink or other value.

Sub FillHyperlinks()
'Must have first cell selected, only one column, at least one empty cell.
With Selection
If IsEmpty(.Cells(1, 1)) = True Then
MsgBox "Your selection must have a value in its first cell." & vbCrLf & _
"Cell " & .Cells(1, 1).Address(0, 0) & " is empty.", 48, "Nothing to copy."
Exit Sub
ElseIf .Columns.Count > 1 Then
MsgBox "Select only one column at a time.", 48, "One column only."
Exit Sub
ElseIf WorksheetFunction.CountBlank(Selection) = 0 Then
MsgBox "Your selection does not contain empty cells.", 64, "Nothing to copy to."
Exit Sub
End If
Application.ScreenUpdating = False
Dim cell As Range, xRow&, xCol&
xRow = .Cells(1, 1).Row: xCol = .Cells(1, 1).Column
For Each cell In Selection
If IsEmpty(cell) = True Then
Cells(xRow, xCol).Copy cell
Else
xRow = cell.Row: xCol = cell.Column
End If
Next cell
Application.ScreenUpdating = True
End With
End Sub

 

[Adiga Spirit]

Thanks a lot, it works like a charm.