Loop through columns and then rows

[Guest]

I have a spreadsheet that needs the text in 2 columns to be altered. I would
like to do the whole spreadsheet with a macro and the number of rows changes.
I have set up a macro that works if I click in the top cell of each column,
but I would like to be able to click in the first cell and have it fix that
cell and then go to the next column and fix it, next column and fix it, etc
until it reaches the end of the row. Then it would need to move down to the
next row and do the same thing. It would do this until it encountered a
blank row. The task necessary to fix the cell is not one where I can
highlight the whole row; it only works on a single cell at a time. Does
anyone have a good bit of code that would work well for this application?
Thanks so much!

 

[Nick Hodge]

Iterations are often simpler if you pre-select the data. If this is
possible the code below will run along the rows and then down the columns
changing what is in the cell to only the first three characters.

If you cannot pre-select, post back

Sub IterateSelection()
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Left(myCell.Value, 3)
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Will it work to preselect the whole sheet or the first three columns (since
that's where the hyperlinks are that I need to get rid of)? Also, if I use
myCell.Value.hyperlink.delete
This is similar to what I'm doing now, except I'm using activeCell.

 

[Nick Hodge]

Yes, although the entire sheet will take some time, you will need to change
the line to

myCell.Hyperlinks.Delete

Although if that is what you are looking to do the this would be better to
kill hyperlinks

Sub killhyperlinks()
Cells.Hyperlinks.Delete
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Wow! The kill hyperlinks thing works really well. If only I had known it
was so easy to do I wouldn't have spent 3 hours trying to figure it out
yesterday!! Thanks!!

 

[Glenn]

Actually, your macro doesn't need a preselected range in order to work,
provided that the criteria for limiting your action -- that is, a blank
column or row to indicate end of iteration -- works throughout your
data range.

If, as you menitoned, you're trying to remove hyperlinks from a range
of cells to the right and below any cell you select, the code could be
written like this:

Sub Remove_Hyperlinks()
Do Until ActiveCell.Value = ""
Start = ActiveCell.Address
Do Until ActiveCell.Value = ""
ActiveCell.Hyperlinks.Delete
ActiveCell.Offset(0, 1).Select
Loop
Range(Start).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Nick's last solution is best if, in fact, you're trying to remove ALL
hyperlinks from the worksheet.

 

[Guest]

This is also very helpful since it is basically what I was trying to do
yesterday and could never quite get it right. Why do you have to put Do
until activeCell.Value="" twice?

 

[Nick Hodge]

Glenn

Even though the route I took deleted all hyperlinks in the worksheet, it
would be infinitely quicker and easier on the eye than the 'Selecting' done
with your code. It is seldom necessary to select anything to act on it and
indeed it needs two statements, 'select and activecell', etc.

Not that this will be the most efficient code for sure, but this will find
the last column used and the last row used and delete hyperlinks in that
range only. (XL97 and up only)

Sub TestRangeAndDelHyperlinks()
Dim iLastCol As Integer, lLastRow As Long
iLastCol = Range("IV1").End(xlToLeft).Column
lLastRow = Range("A65536").End(xlUp).Row
Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Hyperlinks.Delete
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Glenn]

Nick...that's a concise solution, and proves that with Excel, there's
always more than one way to "skin a cat" (horrible idiom, I know).
Glenn Ray