Posts by Ger Plante

Re: Get FileExtension when ><3 caracters

If I understand correctly you just want to isolate the extension of the file and this is tricky as it could be 2,3 or 4 characters.

Why dont you just use INSTRREV function to search for a "."

The INSTRREV function searches from the end of the string and so when it finds the first period (".") you should be able to isolate the last characters of the string based on the position of the period and the length of the string.

God I hope that makes sense :?

How do I calculate the current width and depth of the active worksheet in pixels. I know that VisibleRange.Rows and Visible Range.Columns will tell me the number of rows and columns that are displayed in the window, but not the number of pixels.

"Screensize" is no good to me either as this just shows the size of the screen in pixels, but not the active worksheet....

(I'm just trying to work out the visible boundaries of the worksheet for drawing some lines).

THX
Ger

Hi, I'm just messing with some colors in a bid to be artisitc and found this simple enough code to be surprisingly slow.... I know I'm trying to do this on ALL cells... but could/should it be quicker

Dim iLoop As Integer
    Dim C, MyRange As Range
    
        Randomize
        
        Cells.Select
        
        Selection.ColumnWidth = .5
        Selection.RowHeight = 5
        
        Set MyRange = ActiveSheet.Range("A:IV")
        
        For Each C In MyRange
            C.Interior.ColorIndex = Int(Rnd() * 56)
        Next C
        
    End If
    
    
End Sub

I also tried it with bog standard row,col for loops, but rather predictably, no quicker. . . . . And before you ask, No, I dont need to fill ALL cells, I was just surprised by how slow it was... purely acedemic.

Re: How to build a formula using text and array value?

As a general tip, I find it very useful to use the chr() function when I want to include quote marks in a string. So for example:

strName = Chr(34) & "Bono" & Chr(34)
' results in "Bono" with quotes being stored in strName

Nothing cracks me up more than getting lost with single and double quotes in VB.

Hi All, simple enough problem surely, but its wrecking my buzz right now...

Trying to use AppActivate to launch an instance of a web page in a browser

ReturnValue = Shell("C:\Program Files\Internet Explorer\IEXPLORE.EXE http://www.ireland.com", 1)    'Run Internet Explorer.
AppActivate ReturnValue, False      ' Activate internet explorer.

The Shell executes fine and returns a 'ReturnValue'.
The Appactivate command results in a "Invalid Procedure Call or argument"

It doesnt matter if False is changed to True and it doesnt matter if the window state in the Shell command is changed (currently set to 1).

Thanks for yer help.

I've written some fairly short code to delete multiple (non contiguous) rows based on certain criteria (similar I guess to holding down the CTRL key and clicking on mulitple rows). In other words, the rows could be anywhere on the sheet and not necessarily sitting beside each other. It works 90% of them and I might need a work around for the other 10% :?

In summary, I use the Range.Select method for all relevant rows and when they are selected, the Range.Delete method.

The Range takes the A1 notation, so it would need to look something like Range("1:1,3:3,56:56").Select to select rows 1, 3 and 56.

The generate the row numbers between the brackets and quotes, I keep adding to and build up a string variable called Rows_To_Delete. The condition around building up the string is a simple cell value test on columns 1 and 2 for each row that has data. It works fine and I have not included it below.

'if condition then


Rows_to_Delete = Rows_to_Delete & Row_Counter & ":" & Row_Counter & ","


'end if


If Right(Rows_to_Delete, 1) = "," Then


    'remove the last comma from the string.
    Rows_to_Delete = (Left(Rows_to_Delete, Len(Rows_to_Delete) - 1))


    'select the rows and delete.
    Range(Rows_to_Delete).Select
    Selection.Delete Shift:=xlUp
    Range("A1").Activate
    
End If

This Code works fine in 90% of the cases. However, if the String Rows_to_Delete goes over 256 characters in length then Range(Rows_to_Delete).Select fails miserably. Anyone know why this is? I have tested it and prior to the .Select being executed the string is correctly built and has the appropraite length - sometimes up to 400 or 500 characters long. Rows_to_Delete is defined as type "String"

Anyone know any other alternatives or good workarounds for deleting non contiguous Rows?