Posts by Keithcornett

Re: Opening a file from any active directory

Hi Tony,

I moved the excel file along with the text file into another directory and tried the code but got an error message. Is there something else I need in the excel workbook?

Thanks for your help on this.

The file that this code is located in is often sent to others to use which means I have to modify the directory and path each time I send this file in order for the VB code to work.

Workbooks.OpenText Filename:= _
        "C:\My documents-2\Monthend Supplemental Reports\Deficiency Reports\current\C37.txt"

Is there a way to have the file opened from any active directory it happens to reside in?

Thanks for any help on this.

Re: Transpose data from bottom to top

Hi Tony,

It would probably be a set number of columns, but I would use this for other reports, so it might be variable too. If I know where to go in the code to make the adjustment then the set number would work.

Thanks again for all your help,

Keith

Re: Transpose data from bottom to top

Hi Tony,

When I ran the macro, it did transpose the data but only brought in data from column a. Can I modify the code to select a specific number of columns to transpose?

Thanks for your help,

Keith

Hi,

I have a worksheet that has 15,000 rows of data which is imported from a text file. I want to import it into excel from the bottom up. The paste special function doesn't seem to cover this type of function. Any possibility of having a macro do this?

Thanks for any assistance on this.

Re: Delete all rows below text criteria

Thank you very much. This works perfectly. Once again I've got to say that this forum is the absolute best!

Warmest Regards,

Keith

Re: Delete all rows below text criteria

Hi Tom,

I tried the code you provided but didn't have any luck. The actual content of the cell is: "ACCT# C". Would the # and the space before the letter C be causing a problem? Here's what I've got now and thanks again for your help.

Sub 
Dim LastRow As Long, r As Long 
LastRow = Range("A65536").End(xlUp).Row 
For r = 1 To LastRow 
    If Len(Cells(r, 1)) <> WorksheetFunction.Substitute(Cells(r, 1), "Acct", "") = 0 Then 
        Range("A" & r & ":A" & LastRow).EntireRow.Delete 
        Goto Done 
    End If 
Next r 
Done: 
End Sub

Hi,

I have a macro that uses the code listed below to delete formulas from all rows below any cell in column A that started with zero. I need to modifiy this to use in another workbook to delete all rows once the word "acct#" is encountered in column A. I tried replacing the = 0 with = "ACCT#" but it didn't work and my VBA skills are pretty abysmal. Any help would be greatly appreciated.

Sub    
Dim LastRow As Long, r As Long
    LastRow = Range("A65536").End(xlUp).Row
    For r = 1 To LastRow
        If Cells(r, 1) = 0 Then
            Range("A" & r & ":A" & LastRow).EntireRow.Delete
            GoTo Done
        End If
    Next r
Done:
End Sub

Re: Problems Adding Series to Chart

That's exactly what I wanted the chart to do! Thanks for your help on this.

Keith

Re: Problems Adding Series to Chart

I set up the series for the A.P.C. but how do I assign it to a particular axis?

I have a chart that currently has two series of data. Whenever I try and add an average line or another series of data, the smallest series is no longer visable on the chart.

I want to add an 'average' line and also show the data titled 'Net A.P.C.' on this chart. Am I trying to put more information on a single chart than can be accomodated?

Thanks for any help on this.

K.C. from Los Angeles

Re: Clear all cells in rows below specific condition

Works perfectly!!! Thank you soooooo much....

Keith

Re: Clear all cells in rows below specific condition

Barbara you are absolutely brilliant! This works perfectly. One last question, since when I sort the data, the dates are always in column A, would I need the message input box?

Thanks again for all your help.

Keith

Re: Clear all cells in rows below specific condition

Hi Barbara,

I've attached a sample file showing the data. I don't need any header's because this worksheet is basically a small database that is linked to another worksheet which has formulas that extract the data from it.

The second example you sent works great if there's header data in column A, but as you can see, sometimes the header data will start in column C. The dates are always in column A though.

Thanks again for your help on this. It just amazes me what you can do with VBA (I definitely need to take some classes

Keith

Re: Clear all cells in rows below specific condition

Hi Barbara,

Thanks for your help on this & sorry about the delay in getting back on. I was out of the office most of the day.

I think the logic is correct in that when I sort the data, all the dates go to the top of the file and the header information goes to the bottom. I would want to find the last date and delete all rows below that row.

I'm not very good with writing VBA. Most of my VBA experience comes from recording macros and tweeking them afterwards.

Again, I really appreciate your help,

Keith

Hi,

I've got a macro that imports a text file and sorts it by date. The problem is that all the header data and a lot of other garbage (which I don't want) goes to the bottom. I want to clear the contents of approximately 5000 rows below the last date encountered in column A so all the text and header data is gone.

Thanks for any help on this,

Keith from Cali

Re: Fill cells from bottom up

Absolutely! Thanks for any help on this.

I have a list that has a financial class at the bottom of each section of data. I need to fill in the blanks up from the bottom, example:

A B
1 1
2 1
3 2
4 CLASS 10
5 1
6 2
7 CLASS 14

I want to replace A1:A3 with CLASS 10 & A5:A6 with CLASS 14, etc. But the number of cells varies from day to day . I know how to fill down with the goto / special function, but filling up is not covered. Any help would be greatly appreciated.

Re: Sort by cell format

Wow, that was amazingly fast! It works perfectly. Thanks so much for all your help on this. I really appreciate it!

Keith

Re: Sort by cell format

Hi Rich,

Here's a sample of the file. The actual file has about 5000 rows of data. Thanks again for all your help on this.

Keith