Posts by chatguy

Re: Finding The First Empty Row In A Macro

Quote from turtle44

This will find the last row with any data. (Add 1 to get next empty row) There is no error handling, so this will fail if entire sheet is blank.

Code

lLastRow = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart, _
         SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Thanks so much! Will try this out now...

CG

Re: Finding The First Empty Row In A Macro

Quote from turtle44

Are you trying to find the next row after the end of your data, or do you have blank rows in between your data?

Thankfully there should be no blank rows in between (I imagine that would be much harder), so this would be the next row after the end of the data. However .... if that is done, to make sure that the text won't append in one accidental empty row (followed by a occupied row) probably would be good in that scenario, but if that's too hard to figure out, that doesn't need to be done. --- So in summary, if it's easily doable, then please, but if it's too much work, it's not 100% 'needed'. Thanks again, CG

Re: Finding The First Empty Row In A Macro

Quote from turtle44

The easiest way would be to look at the "Possible Answers" above. I'm sure they answer the same question. A simple search would work also.

Quote from turtle44

The easiest way would be to look at the "Possible Answers" above. I'm sure they answer the same question. A simple search would work also.

Thanks Turtle. These were the only two I could find when doing the search before posting.

...Since I consider myself fairly new with VBMacro scripting, I wasn't able to come up with an answer based on those searches. My closet attempt was this:

Dim eCell   As  Range 
With Range("A1:A9999") 
    Set FirstEmptyRow = .SpecialCells(xlCellTypeBlanks).Cells(1, 1) 
     MsgBox eCell.Address 
End With

The problem is that I'd like to search for the first row, but not the first empty cell, since some rows will have some blank cells within the row. But then, if possible, I'd love it if I can then assume that column A can be marked as the first cell within that first empty row.

Hi All,

Would anyone know how to search for the "First Empty Row" in a VB Macro?
And if possible, can this be copied into a variable (maybe called: FirstEmptyRow) in the format of: $A$31 (for Row 31) or $A$99 (for Row 99), for example?

(The reason for this, is because I would like to make a macro that would search for the first unused row, then append C:\append.csv to that particular row, in column A)

Thanks so much,
CG

Re: Column Width Macro

Quote from shg

You're welcome. Why not just do

Code

Columns.Autofit

Mainly because I wanted to allow the columns to fit on a printed-out page horizontally. If any text is too large, I have the columns set to wrap to the next line in the cell.

Thinking of how you showed that I didn't need to highlight the columns going across, I'm also probably doing the cell wrapping inefficiently as well (wanted to wrap the text in the cells from Row #4 down to the rest of the spreadsheet. I currently have:

Rows("4:4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection
        .VerticalAlignment = xlTop
        [b].wraptext = True[/b]
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A4").Select

...but have a feeling that I can do something similar to what you've shown me above (thank you again, btw!), so that I wouldn't need to highlight those rows in order to wrap text within those rows.

CG

Re: Column Width Macro

Thanks shg! You're awesome! This worked perfectly! ...and even better, it's more efficient. I like the fact that cells don't need to be highlighted beforehand, and even don't flicker anymore on execution. Thanks again for the very fast reply!

CG

Hi All,

I've used the macro recorder to try to auto-apply column widths after I do a CSV import:
(A&B=3.14, C=8, D=13.57, E=24.14, F=9, G=10, H=11.29, I=8.57, J=6.86, K=8, L=13, M=10)

...but for some reason when I execute this macro, "every" column gets the width of 10!

May I pls ask what I'm doing wrong, or how I can fix the below code?

Sub SetColumnWidth()
    Columns("A:B").Select
    Selection.ColumnWidth = 3.14
    Columns("C:C").Select
    Selection.ColumnWidth = 8
    Columns("D:D").Select
    Selection.ColumnWidth = 13.57
    Columns("E:E").Select
    Selection.ColumnWidth = 24.14
    Columns("F:F").Select
    Selection.ColumnWidth = 9
    Columns("G:G").Select
    Selection.ColumnWidth = 10
    Columns("H:H").Select
    Selection.ColumnWidth = 11.29
    Columns("I:I").Select
    Selection.ColumnWidth = 8.57
    Columns("J:J").Select
    Selection.ColumnWidth = 6.86
    Columns("K:K").Select
    Selection.ColumnWidth = 8
    Columns("L:L").Select
    Selection.ColumnWidth = 13
    Columns("M:M").Select
    Selection.ColumnWidth = 10
End Sub

Thanks in advance,
CG

Hi All,

May I ask how to write a search and replace macro, but instead of writing:

Cells.Replace "OldText", "NewText"

... may I ask how to independently read this "search" text into a string (hopefully a string that can handle 'more' than 255 characters per cell, even in Excel 2000) then replacing that search text("OldText") with ("NewText") step-by-step?

(Also pls. note that "OldText" can appear multiple times in one cell)

Thanks so much in advance!!
CG

Re: Sum Values In Column If Condition Met In Another

Thanks Richard,

That works perfectly! I had a feeling it may have been a short line, but didn't realize it was that short! :oops: Thank you so much for your help!!

CG

Hi All,

If anyone has a moment, I have a question that I think 'should' be easy to solve(?) except with my knowledge, seems to be a little more of a challenge:

If possible, I would love for the answer to be a "formula" and not a macro, but if it must be a macro, then that's doable too:

Basically, I would love a formula that would add all the numeric values in "Column A", as long as "Column B" in that same row="YES".

So...

4 YES
6 YES
7 NO
9 YES

would mean 4 + 6 + 9 = 19

....This will cover a variable amount of rows, but it will definitely be less than a few hundred.

Thanks so much in advance!
CG

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Dave Hawley

chatguy, please use code tags as YOU agreed.

Done. Apologies. I simply forgot to do that. That's all...I did not mean to violate any agreement by forgetting once.

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

Do you want it to happen on a specific sheet or for any worksheet in a workbook?

If it were me doing the typing, I'd shorten the string to: %LF

...or are you copy/pasting it in there?

It would be nicer if it worked on any sheet in the workbook, but if it's easier to do on a specific sheet only, that's OK too.

The data already exists in a .CSV file (and already has the %newline% in the cells). Because the data isn't always in the same cells, it's a lot easier to do a quick copy/paste from some cells in the .CSV file into this workbook with the macro.

Having the new lines convert automatically without manually running the macro each time makes things a lot easier since I copy/paste different cells from that file often.

Thanks in advance again!
CG[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I think I've got it. I've attached the code below for anyone that might stumble into this thread in the future. Also thanks again to Aaron and Turtle who I could have not done this without!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Cells.Replace " %newline% ", vbLf
Cells.Replace "%newline% ", vbLf
Cells.Replace " %newline%", vbLf
Cells.Replace "%newline%", vbLf
Application.EnableEvents = True
End Sub

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

I like Turtle's starting point.

A few extra lines should take care of the blank spaces... Logically, you just always look for the biggest strings first.

Code

Sub Test()
    Cells.Replace " %newline% ", vbLf
    Cells.Replace "%newline% ", vbLf
    Cells.Replace " %newline%", vbLf
    Cells.Replace "%newline%", vbLf
End Sub

Thanks Aaron & Turtle! This works pretty nice! I had a feeling it was going to be relatively short code, but didn't know it done in just 4 lines! Pretty nice!

I just have 1 more question to ask though, if OK:
May I ask how to make the conversion happen on-the-fly? (meaning, can it convert as soon as I hit enter after typing %newline% into the cell?)

Thanks again for your help!
CG

Re: Auto Replace A Text String With A "newline" (like an Alt-Enter within a cell)

Quote from Aaron Blood

Do you want to automatically scan an entire sheet or work within a specified range?

Do the entries look like this:
"Hello%newline%There"

...or this:
"Hello %newline% There"

...and in the second case, do you wish to preserve the leading and trailing blank spaces?

Display More

I'd like to scan the entire worksheet, if possible (no specific ranges). The original can look like either case above (with or without leading and trailing spaces) and can appear multiple times in each column -- It would be nice to remove the leading/trailing spaces, if they exist... but it's not totally needed if it's too hard to figure out how to do. -- Thanks so much in advance!

CG

Hi All,

I couldn't find another question like this in the forums, but hopefully this is fast and easy to do.

Would anyone know:
How to write a macro that will auto-replace the text string: %newline% with a new line within a cell (preferably on-the-fly) (similar to hitting ALT-Enter within a cell).

The %newline% text string can appear in any cell in the workbook, and can also happen more than once in a cell.

Thanks very much for your help!
CG

Re: Cell Hyperlinks Based On Cell Contents

Thanks Reafidy!

Actually I like yours even better than the first, because you can use it in any sheet (the other one was limited to one sheet at a time). Still, I'd like to thank AAE and Corine for their help! (And I'll have to admit, while I apologize for my screwup, that I'm surprised as many came to help, and within the same day actually. After all this, you can be sure I'd only need to connect to Ozgrid in the future.) Links are already pointing here from the other two sites, but I will also give you credit on the other sites and will post your answer there as well.

Thanks again,
cg

Re: Cell Hyperlinks Based On Cell Contents [solved!]

As promised, here is the answer to the question. I'm unable to link to their web site since that thread has been closed, but I'd like to thank Corine Reyes for the answer!

Private Sub Worksheet_Activate()
Dim stext As String
stext = Worksheets("Sheet2").Range("C2")


With Worksheets(2)
   .Hyperlinks.Add .Range("B2"), "http://www.google.com/search?q=" & stext
End With
End Sub

In sheet 2, enter the following:
B2 = google search
C2 = Test (or any word you wish)

Save the workbook, when sheet2 is activated, the above code runs.

Re: Cell Hyperlinks Based On Cell Contents

*off-topic

Apologies all--

I have skimmed over the agreement the first time around, reading about "crossposting", but thinking it only meant not to flood all the channels of this same web site. I'm not the type of person who would have done that anyway. However, now that I know what crossposting is, if I post another question on this web site, I will now wait 48 hours before asking elsewhere. Apologies to anyone I might have offended by asking the same question somewhere else on the Internet.
I've noticed that the links are posted above, so I shouldn't flood this thread with the same links (as it's getting pretty long already), so please go there, or look below at someone else's post for the answer or look for my post with the answer from elsewhere. Unfortunately I have not received an answer as of yet, but if I do receive an answer elsewhere, I will copy the answer to this web site as well to make it easier for someone else who may be looking for the answer in this thread.

*back on-topic below:

Re: Cell Hyperlinks Based On Cell Contents

Thanks AAE! That's it! I was able to put it in the next column over to make the link...

But....
By any chance, would you or anyone else know how to do it with a Macro?

(I'm sorry--I know I asked for preferably a formula, but I realized it takes up too much space when printing.) A macro should make this work, by just making that text added in L4 simply a hyperlink.

Hi all,

-> In Excel Cell L4 to L9999, I would like to have Excel have that cell Hyperlinked of a Google search of the contents which were entered in that cell. (or, if that same cell can't be Hyperlinked, then a neighboring cell is OK)

-> So, for example, if cell L4 contained the word: "testing",
I would like to be able to click on that text in the cell to open a browser and take me to: "http://www.google.com/search?q=testing"

...I hope this can be done without writing a macro (maybe by a formula instead?) BUT, if it's the only way of doing it, then yes, please, in that case, I would LOVE to know how to write/enter the macro.

Thanks very much,
CG