Hi,



My 2 cents again. Hope I'm not interfering too much.
You mentioned

I think the reason is if in the range you specified there in no data the code will take you to the "Last" row in that column. If however, you have date in row 20 you will then see that as the "Last" row.

Added: I too learned the "Old Fashion" way. Trial and error.

not interfering at all! I appreciate anyone's input in helping me get this accomplished and understanding the code and different functions etc. I did what yo usaid- I put my cursor in a column that had a series of blank cells and cells containing data. It did what it was supposed to do. On mine, for column H, I had data in rows 1, 5, 6 and 7. So rows 2-4 are blank. With my cursor in cell H1, I hit End Down, and it brought me to cell H5. If I hit End Down again, it brings me to cell H7. The next cell that has data in it doesn't come along until H19- and when I do End Down again, it bring sme from H7 to H19... and so on.

So, I understand what the purpose of End Down is now! Thanks for the 'interference'

Hi,

Here is 2 more methods to get the "Last" row.
(Not sure if Bob mentioned this)
The first one uses a excel function for "Special Cells
The second will look from the bottom of the column to the first row with data.

Hope these help you..


I use the second code often. If you want to know the "Last" unused row in a column you can add +1 after the row.
You can use this if you need to add data to the column.

Sorry, Bob, that I'm jumping the gun here. But I was trying to do something on my own (you know, my whole trial and error method). Can you look at the code below and tell me what I'm actually telling the program to do? haha

SelectRange = ("E2.End(xlDown)")
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))
TrailingMinusNumbers = True
SelectRange = ("F2.End(xlDown)")
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))
TrailingMinusNumbers = True
Columns("E:F").Select
Selection.NumberFormat = "ddd, mmm dd"

This code actually deleted the contents of columns J, K, and L. Didn't color the rows based on our previous criteria of Phase and Sub-Phase, and left me with columns E and F highlighted but not formatted (the export itself presents the dates as, for example, Fri 6/6/14. And it is just "general"- so I cannot format it unless I delete the preceeding 3 characters, and format the rest as a date).

My intent:
1. Start with Cell E2, and select E2 to the "last" cell (using your handy dandy End.(xlDown) !)
2. Text to Columns: Fixed Width. Split after first 3 characters. Do not import the first 3 characters, and import remaining column as a Date
3. Then, start with cell F2, and select F2 to the "last" cell.
4. (repeat step 2)
5. Format columns E & F as "ddd, mmm dd".

I tried a number of different things to get it to do what I wanted it to. One being:

Set cz = Range("E2").End(xlDown)
SelectRange = cz
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))
TrailingMinusNumbers = True
Set cz = Range("F2").End(xlDown)
SelectRange = cz
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))
TrailingMinusNumbers = True
Columns("E:F").Select
Selection.NumberFormat = "ddd, mmm dd"

However, when I did that- it, for a reason unknown to me anyway, took the data in columns A and B and at a minimum reformatted it to the number format I suggested. So I know I at least have the formatting correct! It's performing the text to columns in the appropriate cells that I am clearly not presenting correctly.

You can respectfully choose to skip this post for the time being, considering we were working on something else before I decided to go off on my own rampage

I had actually written the code several different ways before I even managed to get the Macro to run (trial and error!!). When it finally did, I tweaked it several more times before I actually realized a result (T&E!). And from there, I tried the 2 above examples and could not go any further, as I did not know what was causing it to fail (just E, here).

Thanks! Bob did mention that there are many ways to write code, and that everyone has their own preferred way of doing so. So, I appreciate the other options. I'll use whichever method I find I can memorize easier

I love that you jump the gun and try it yourself. You may remember my saying, or you may have divined from the extreme length and verbosity of my emails, that I'm more about teaching than I am about just doing it.

I gotta run out in about 20 minutes, but I thought I should come back and maybe chime in a little if I can. Let's take a look:I wouldn't expect this to work. What it does is create a variable named SelectRange and tries to set it equal to "E2.End(xlDown)", which is a valid statement but doesn't do what I'm sure you meant to do. Anything in quotes is a character string, you see, including that. What you want is not the letters that spell out "E2.End(xlDown)", but the actual result of E2.End(xlDown)—assuming that E2 is a valid Cell or Range in Excel. Your subsequent attempt is better:That starts with Range("E2") (where "E2", in quotes, describes the Range that you want Excel to work with), which gives you cell E2; then to E2 it does End(xlDown), which gets you the cell that would result if you manually did <End>,<Down> from E2 in Excel. So far, so good.

Then you said "SelectRange = cz", which I expect will work but not the way you think. Here's An Important Fact: When you're setting SelectRange to a simple value, such as 2 or "Sub-Phase", you can use a statement with just an equal sign. But when you want to point to an object, you must use Set, ie "Set SelectRange = cz".

So what's an "object"? Well, let's think about a cell for a moment: A cell is not just the value that's in it; it has many other properties as well. E2 in the worksheet I'm looking at has a value (41771), but it also has a font (Calibri), a font size (11), some color information, several borders (Top, Bottom, Left and Right), a format ("ddd, mmm dd", which causes it to display 41771 as "Mon, May 12") and a ton of other things that you don't normally think about until you need to change it. Each of these "properties" can be manipulated because cz is not just the value of the cell ("Mon, May 12") but the whole cell "object", containing all those properties and also a number of things you can do with the cell (called "methods") such as End(xlDown).

Cz points to cell E2; if you had set "Set SelectRange = cz", Excel would have understood that you wanted SelectRange to point to cell E2 as well. But you said "SelectRange = cz", without the "Set" command, so Excel supposed you wanted to assign some property of cz to SelectRange. You could, for example, have said "SelectRange = cz.Font.Bold", and SelectRange would then be False (because the value isn't in a bold font). Or you could have said "SelectRange = cz.Row", which would have set SelectRange to 2 (the row number).

But you didn't specify the property that you wanted, so instead of getting upset, Excel used the default property. The default property of a cell is its value, ie "SelectRange = cz.Value". So I expect that SelectRange became "Mon, May 12".

Now, I approve very much of you playing with this and asking questions, and I want to answer all of them. But I have to run to a meeting, so I'll finish your questions and take more when I get back, maybe late tonight.

I'll wait until that lesson. me playing around with it is starting to get frustrating lol.

I get frustration (really, I do). What I think would be a good compromise is if you try something, and when it doesn't work tell me about that. Then you can either quit, or try something else, whichever you prefer. But if you don't get me (or someone) to explain it, then the frustration just sits there, instead of pushing you through to the victory.

(I think of it as a victory. Maybe "solution" would be a more sedate word. But I exult with Boris, the evil hacker in Goldeneye: "Yes! I am eenVEENcible!!")

Ok, let me back up to an earlier post of yours, timestamped 06-17-2014 01:44 PM:If you're asking why I'm think it's important to look for the last row, there would seem to be two alternatives:

1) Let the program run until it reaches the end of the worksheet. But that's a million rows, and you would definitely notice the delay while it checked column L of every row until it got to the end (and abended there). Much quicker to give it some sort of stopping point.

2) You could also start at row 2 and keep going until L<row> is empty. That's definitely a possibility, assuming your data won't ever have any blank values in that column. Would you rather go that way? It should work just fine, if you want to see how it's done.

06-17-2014 01:49 PM:Of course! Sounds like you're ready to do that next, so I'll go back to your worksheet and figure something out before going over that code you were working on last time.

I typically sit there for quite a while trying several different combinations of what I think the program will accept. I think the TextToColumns action is a bit too tricky for me to tackle on my own right now. I have set several other little stupid things while awaiting the next lesson. (Auto fit columns, add the view filter.. Easy things ). The record macro button helps, naturally. I record something, see what it does, and then apply. I did the same with the texttocolumns / formatting, but cant seem to get it to do what I want. Tried to apply your lessons and what he code recording showed me, but I guess I'm missing something. Oh well , not important at the moment- we will get there. Grouping and formatting take up the most time of this export anyhow. Either one can occur first - I just had some time to kill.

Thanks again. And , [un]fortunately I dont work on Friday 's but will pop on anyway if I see you post.

One of the things that used to drive me nuts about VBA was the error message "Object doesn't support this property or method". I had just used that property or method right over here in this other part of my program!

My problem, as it turned out, was that I already know about programming so I thought I knew what a "method" is. I didn't, and it was causing me no end of frustration. After a few years of this, a new coworker talked me into writing my own object, and the light dawned: A method is not a subroutine.

If you don't write programs already, then maybe my prejudice isn't yours. But if it is, let me know and we'll talk about it. Meanwhile, let's identify those rows we're going to group together. Here's a bit of the code that worked for me. Note the explanatory comments:I wrote two "functions" to make this easier to look at. A function is subroutine that returns a value to the program, just like what you've already seen in Excel itself. But maybe you've never written one of your own, so let's start by describing them.

One of the functions, named StartRow, is expected to tell the main program which row starts the next group. To do that, it has to start at a given row and look downward until the next time it sees "Sub-Phase" (if you're doing minor groups) or "Phase" (if you're doing major groups). So StartRow expects you to tell it a starting row and the label it's looking for ("Phase" or "Sub-Phase"). It also needs to know which is the last row, so it doesn't go on too long. In the function I named those three arguments "After", "Label" and "LastRow". Knowing those three things, it can start on the row after "After", and keep looking down the rows until either column L has the "Label" value in it, or we've passed LastRow. Once it's stopped looking, it checks the row number; if we're past LastRow then StartRow returns 0, but otherwise it returns the row number just after the Label it found. The calling program thus asks for StartRow and gets back the first row after "Phase" or "Sub-Phase".

The other function, EndRow, does the other side of this; you feed it After and LastRow, and it looks for the next "Phase" or "Sub-Phase" or the end of the data. It returns the row just before that, which is the last row in the group.

The main routine starts by assuming the fictitious "previous" group ended in row 1. There is no previous group at the beginning, of course; we just have to tell the program something so it knows where to start. Then I start going around and round (that's the Do...Loop part), calling StartRow and EndRow and grouping the rows between them.

Now, I didn't finish this program; all it does is the minor groups. One thing at a time, you see; I'm hoping that you'll study this and see how it works—well, see that it works, first, then see how—and then either you'll figure out how to do the major groups yourself, or you'll at least be better equipped to understand the next-stage explanation.

Officially, it works. It also only grouped the first couple of sections at first, to which I then realized I did have some blank cells in Column L (and I shouldn't!). So it helped me clean that column up as well. I filled the cells, and reran it and it worked beautifully to group the sub-phases together. I will make an attempt at grouping the "Phases" for myself and let you know if I run into any problems.

I guess my current struggle is that I have never written code before, so knowing what is a function, what is an object, etc- and actually understanding those definitions are what I need to grasp. I think I need a 'programming for dummies' book- me being the dummy..
Currently, I can look at the code you've written, read your explanations and say 'ok, I understand how to read that code now' - but it's the understanding of how to use it again later if I so choose to write another macro later in life, and being able to understand how and when to use something. Not to mention knowing the language and knowing what I can and can't write for the program to understand. I have a basic understanding that you can basically type anything as long as you define what it is. Although you use 'rz' here, you could technically type 'dog' (it just so happens my dog is sitting next to me..) as long as the definition follows what 'dog' is. Unless there is an underlying rule that says it must be 2 characters in length, therefore using the 3 characters 'dog' wouldn't work (am I right in saying 'you can type anything as long as you define it'?)
Having no prior knowledge of VBA, or writing code (besides tinkering very little with SQL Server..and I do mean, very little), and literally throwing myself into it is difficult, but I am all about simplifying and 'automating' things. Spend the time to do it once, and from then on, click a button. Prior I had the luxury, if you want to call it that, of explaining to someone else what i wanted, and they would write it for me. So it is exciting to attempt these things for myself and also understand, to a degree, what my previous coworkers encountered when performing the background work for me.

Anyhow, just popped on because i saw your post. If i have time today i will work on doing the same for the "Phases" portion. I don't think I'll have a problem, but you never know.

Next up: TextToColumns ?

Thank you!!

Time to point you to some documentation:

1) An on-line reference to VBA for 2010: http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx. This tells you most of what you need to know about the features of the VBA language itself: the statements, the data types etc. For instance, you mentioned the issue of what counts as a valid variable name in VBA; there's a definition at http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx (first character must be a letter, no more than 255 chars long, a list of characters you may not use in a variable name etc). You'll probably have some trouble finding what you want to know at first, so don't stop asking; but bookmark some of your favorite pages and come back to read more from time to time.

2) An on-line reference to the Excel object model: http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx. Ok, "object model" probably doesn't mean anything to you yet, but it's all about the Excel objects that you can manipulate with VBA, and each object's properties, methods, events and other such things that you haven't learned about yet.

The difference is this: In #1, about the VBA language, you learn general concepts. But the VBA language can be used to manipulate many different applications, not just Excel but also Access, Word, Outlook and lots of other objects that have nothing to do with MS Office. When you're programming for Excel you have to know about worksheets, rows, fonts, formulae, borders, conditional formatting and so forth; that's all described in #2. When you're programming for Word, you have no need to understand worksheets but you do need to understand paragraphs. Excel, Word and Outlook all have something called "styles", but they're different in each one—a different team of programmers designed styles in each, so knowing how to manipulate styles in Excel won't help you to do the same in Word. The VBA language is the same, but the objects are different.

(You don't have to learn to program in Word, presumably; I'm not trying to confuse the issue, only to explain the difference between the VBA language and the Excel objects.)

3) Some help (I don't think much of it) for using the VBE Editor itself: http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx. The documentation may be more help to you than it is to me, but however you learn it the editor itself, where you type in the code, can be a tremendous help to you in debugging it. I haven't mentioned it before, but it's time. If you don't already know how to (for example) step through your program one statement at a time, and stop at any point and look to see what value is in rz, or what font is being used in cell E3, it's well worth knowing.

From now on when we talk about how to use the Group method, use the For...Next statement or write a MsgBox announcement to yourself, I'll start pointing to the documentation so you can read about it yourself instead of having to be satisfied with the little bit I tell you.

---

TextToColumns is (in my opinion) pretty complex. I've done it a few times in VBA, but I don't do it cheerfully. You can definitely learn it; but I'll be happier teaching you simpler things at first, until you're more comfortable with some other concepts. For example you wonder about the difference between a subroutine, a function, a method, and object and so on; for my money that's part of the foundation that you should lay before getting very far.

I am NOT saying you must learn all the basic stuff before learning anything more advanced; that's one of the most boring ways to learn anything I've encountered. But if you're trusting me for your education (and you needn't, and eventually shouldn't—I mean, eventually you should either insist I answer your questions or go looking around for other help), I'd wait on TextToColumns a while longer, do it manually for now.

What I'd like to do next is a) answer some of the questions you've already asked, about code you mentioned below and about anything new you write (whether it works or not); b) make sure you get the grouping finished; and c) show you some additional features of the VBA Editor, such as the Watch Window.

I guess this serves just as well:
DELETED

Deleted Message!

*Note: I wish I cold delete previous posts so you don't waste your time responding to them. I am here for 8hrs each day, so I tend to fiddle with things throughout the day. I managed to Group the Phases successfully. I'll try to figure out if I can delete, in which case, if you don't see any posts preceding this one, then I figured that out too!

Now, I'm sorry, but I can't give up on this whole 'TextToColumns' piece.. it actually kind of gives me something to do while awaiting responses

I did a lot of googling today, and at least managed to select the range of E2 End(xlDown)- so that much I have figured out! I even found out what Array 0, 9 and 3, 3 means. That it will skip the first column, and format the second to the date format of MDY (9= xlSkipColumn, 3 = xlMDYFormat ). But what i can't quite understand is why it won't actually do the task of splitting the data out. I feel like I will eventually figure it out for myself, but at the same time, whatever I return in my google searches, I'm never quite sure I'm looking at the right thing haha.

Now my code appears as:

Range("E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns
Destination = Range("E2")
DataType = xlFixedWidth
FieldInfo = Array(Array(0, 9), Array(3, 3))

My last thought to this is: is "TextToColumns" a FUNCTION? and perhaps if I identify it as such, it will actually perform the task?