Good Day,

I am looking for the VBA that will will count the total no. of records
(rows) in a spreadsheet dynamically and will then select a cell range in Col
E from E2 to total count value. This macro will be used on multiple
spreadsheets with varying row counts.

Background:
I have several spreadsheets being exported from queries in a Microsoft
Access Database. The data contains URL to files; however in the export
process the URLs are converted from links to a format that looks like the
following [ using google.com as an example] Data export looks like this:
#www.google.com# as are formatted as text

I am using the followin macro to strip the pound signs off the URLs and
convert them to usable links and do so dynamically to the end of the column.
The macro works fine except the problem is, the initial data export only
contains about 350 rows, after I run the macro, the macro converts all the
existing URLs well enough, but then continues to generate thousands of rows
of blanks making the record count about 65000 rows (mostly blank data).

Sub Database_Format_Temp_Links()
'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="#", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("C:C").ColumnWidth = 44.57
Columns("E:E").ColumnWidth = 40
Columns("F:F").ColumnWidth = 35

'Make Links
'Select cells in column from Row 2 to End of Col. Alter Letter to adjust
column
Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row).Select

'Make Links from export
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub


To resolve creation of the additional extra blank rows, I am thinking of 2
possible solutions:

a.) find the code that will first count the total no. of records in the
file, then I can use a range select from Row2 downto the total record count
to apply the hyperlink adjustments,

Or,

b.) use the current macro but then include code to search for the last non
blank cell, then select from the row below to the end of sheet
(similar to Cntl + Shift + End) and then delete all blank rows.

Thanks in advance for your assistance.