从列添加最后一行中的超链接(Add Hyperlink in Last Row from Column)
我正在使用Excel 2013并希望通过VBA实现目标。
我有一个工作表(表1),一个FileDialog和一个AddHyperlink函数。 我想要做的是在A列的第一个空行中添加超链接到工作表。
Sub addLink() Dim ws As Worksheet Dim rng As Range Set ws = Sheets("Tabelle1") Dim fd As FileDialog Dim selectedPaths() As String Dim I As Integer Set fd = Application.FileDialog(msoFileDialogOpen) Set rng = Sheets("Tabelle1").Range("A1") With fd .AllowMultiSelect = True .Title = "Select your File(s)" .InitialFileName = "" If .Show = -1 Then ReDim selectedPaths(.SelectedItems.Count - 1) For I = 0 To .SelectedItems.Count - 1 selectedPaths(I) = .SelectedItems(I + 1) With ws .Hyperlinks.Add Anchor:=rng, Address:=selectedPaths(I) End With Next I End If End With Set fd = Nothing End Sub这段代码工作正常。 只有我无法建立的是最后一行。 我看过,我需要这样的东西
Sub LastRowInOneColumn() Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With End Sub任何人都可以帮我把这两个代码块放在一起吗? 提前致谢。
I'm using Excel 2013 and want to achive a goal with VBA.
I have a Worksheet (Table1), a FileDialog and a AddHyperlink-Function. What I want to do is adding the hyperlinks to the sheet in the first empty row in column A.
Sub addLink() Dim ws As Worksheet Dim rng As Range Set ws = Sheets("Tabelle1") Dim fd As FileDialog Dim selectedPaths() As String Dim I As Integer Set fd = Application.FileDialog(msoFileDialogOpen) Set rng = Sheets("Tabelle1").Range("A1") With fd .AllowMultiSelect = True .Title = "Select your File(s)" .InitialFileName = "" If .Show = -1 Then ReDim selectedPaths(.SelectedItems.Count - 1) For I = 0 To .SelectedItems.Count - 1 selectedPaths(I) = .SelectedItems(I + 1) With ws .Hyperlinks.Add Anchor:=rng, Address:=selectedPaths(I) End With Next I End If End With Set fd = Nothing End SubThis code is working and doing good. Only thing that I can't build is the last Row. I have seen, that I need something like this
Sub LastRowInOneColumn() Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With End SubCan anyone help me bring this two code blocks together? Thanks in advance.
最满意答案
代替
Set rng = Sheets("Tabelle1").Range("A1")使用
With Sheets("Tabelle1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set rng = .Cells(LastRow + 1, "A") End WithInstead of
Set rng = Sheets("Tabelle1").Range("A1")use
With Sheets("Tabelle1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set rng = .Cells(LastRow + 1, "A") End With更多推荐
发布评论