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.

Hi,
maybe posting in the programming forum might yield better results?


--
Pecoflyer

Cheers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=30617

If you ever had formulas that evaluated to "" (and converted to values) in that
column, then the cells that look blank aren't really empty.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

I assumed that the data ("#www.google.com#") is in column D. Modify that if you
need to:

Option Explicit
Sub testme()

Dim myCell As Range

With ActiveSheet
With .Range("D:D")
'remove the # characters
.Replace what:="#", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'change those "" to real empty cells
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With

For Each myCell In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
If myCell.Value = "" Then
'do nothing
Else
.Hyperlinks.Add Anchor:=myCell, _
Address:="HTTP://" & myCell.Value
End If
Next myCell
End With

End Sub

Brent E wrote:

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.

--

Dave Peterson

Good day Dave,

Thanks for the great assistance. Looks like this could be really good to
clear up extraneous data rows and then run the macro w/ the real data.

The only issue I see is that to do so, I'd need to manually open each file,
select the range from below the last row of official data to the end and then
run the macro to clear up the apostrophes.

I need to find a way to automate this to take out the manual range selection
element.

I found the following code that identifies the address of the last data cell.

Dim LastCellAddress As String
LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Address

I also found this code as a way to select the data either in a range of cols
or a specific col.

Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select

This will effectively identify where the data stops. The problem is I don't
know the syntax to add a (row+1) to the address value to identify where the
deletion needs to start once the apostrophe records are created by the
hyperlink module.

I was thinking before running the hyperlink macro, we could find a way to
adapt the lastcelladdress to identify the last row of data +1. Pseudo code
maybe something like:

' After data export and before running the hyperlink code that generates all
the apostrophe records, do:
LastDataCell = ActiveCell.SpecialCells(xlLastCell).Address
'Then we'll know what row the data stops, and once the macro runs
blanks will start at LastDataCell(Row+1)

'Then we can select from LastDataCell(Row+1) to end of col;
Something like
Range(LastDataCell(Row+1) to Selection.End(xlDown)).Select
Rows.Delete

What are your thoughts?

Thanks,


"Dave Peterson" wrote:

If you ever had formulas that evaluated to "" (and converted to values) in that
column, then the cells that look blank aren't really empty.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

I assumed that the data ("#www.google.com#") is in column D. Modify that if you
need to:

Option Explicit
Sub testme()

Dim myCell As Range

With ActiveSheet
With .Range("D:D")
'remove the # characters
.Replace what:="#", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'change those "" to real empty cells
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With

For Each myCell In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
If myCell.Value = "" Then
'do nothing
Else
.Hyperlinks.Add Anchor:=myCell, _
Address:="HTTP://" & myCell.Value
End If
Next myCell
End With

End Sub

Brent E wrote:

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.

--

Dave Peterson

I don't think you have to run anything special. The sample code include a
couple of lines that would clean up that detritus (those two edit|replaces).

If you wanted to clean up all the cells with those apostrophes, you could use:

dim DummyRng as range

'change those "" to real empty cells
with activesheet
.cells.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.cells.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
set dummyrng = .usedrange 'try to reset last used cell
end with

And by clearing up that detritus, the .end(xlup) should stop at the real last
used cell. (I wouldn't want to loop through all the cells if I could do it in a
couple of edit|replaces.)


But you can use this to determine the last used cell. I like to pick out a
column that I know always has data and use that, but you could use
..specialcells, too.

Dim LastRow as long
dim myRng as range
with activesheet
'this goes after the code above

'using column A to get the last used row
lastrow = .cells(.rows.count,"A").end(xlup).row

'or using .specialcells (don't use both)
lastrow = .cells.specialcells(xlcelltypelastcell).row

'to assign a range:

set myrng = .range("a2:E" & lastrow)
'or
set myrng = .range("a2:E" & lastrow + 1) 'not sure why you want +1

end with

It's not usually required to select something (a range, worksheet, object) to
work with it. In fact, it'll slow down your macro and even worse, makes it more
difficult to understand (for me anyway).



Brent E wrote:

Good day Dave,

Thanks for the great assistance. Looks like this could be really good to
clear up extraneous data rows and then run the macro w/ the real data.

The only issue I see is that to do so, I'd need to manually open each file,
select the range from below the last row of official data to the end and then
run the macro to clear up the apostrophes.

I need to find a way to automate this to take out the manual range selection
element.

I found the following code that identifies the address of the last data cell.

Dim LastCellAddress As String
LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Address

I also found this code as a way to select the data either in a range of cols
or a specific col.

Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select

This will effectively identify where the data stops. The problem is I don't
know the syntax to add a (row+1) to the address value to identify where the
deletion needs to start once the apostrophe records are created by the
hyperlink module.

I was thinking before running the hyperlink macro, we could find a way to
adapt the lastcelladdress to identify the last row of data +1. Pseudo code
maybe something like:

' After data export and before running the hyperlink code that generates all
the apostrophe records, do:
LastDataCell = ActiveCell.SpecialCells(xlLastCell).Address
'Then we'll know what row the data stops, and once the macro runs
blanks will start at LastDataCell(Row+1)

'Then we can select from LastDataCell(Row+1) to end of col;
Something like
Range(LastDataCell(Row+1) to Selection.End(xlDown)).Select
Rows.Delete

What are your thoughts?

Thanks,

"Dave Peterson" wrote:

If you ever had formulas that evaluated to "" (and converted to values) in that
column, then the cells that look blank aren't really empty.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

I assumed that the data ("#www.google.com#") is in column D. Modify that if you
need to:

Option Explicit
Sub testme()

Dim myCell As Range

With ActiveSheet
With .Range("D:D")
'remove the # characters
.Replace what:="#", replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
'change those "" to real empty cells
.Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
End With

For Each myCell In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
If myCell.Value = "" Then
'do nothing
Else
.Hyperlinks.Add Anchor:=myCell, _
Address:="HTTP://" & myCell.Value
End If
Next myCell
End With

End Sub

Brent E wrote:

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.

--

Dave Peterson


--

Dave Peterson