ChrisGreaves wrote: ↑11 Oct 2020, 17:31
....(Now I have to find a use for this. Grrrr!
)....
Code: Select all
Sub test1()
.....
Sub test6()
Range("A1:b2", Range(Range("d6", "e7"), "g8")).Value = "Frederich"
...
I can’t think of any thing much use, yet, Chris.
I suppose that two argument syntax version of
Range( ) is returning
what would be the
UsedRange. So if you are possibly going to use two single area ranges, and want to know what the
UsedRange will be, then that would be one use.
Code: Select all
Sub UsdedRangeToBeOrNotToBe() ' http://www.eileenslounge.com/viewtopic.php?p=275972#p275972
Dim Rng2BorNot2B As Range
Set Rng2BorNot2B = Range("C4", "A1:A10")
Dim Rws2BorNot2B As Long, Clms2BorNot2B As Long: Let Rws2BorNot2B = Rng2BorNot2B.Rows.Count: Let Clms2BorNot2B = Rng2BorNot2B.Columns.Count
' Not used yet, even though I "got it" by Setting it
MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and UsedRange column count is " & UsedRange.Columns.Count & " and UsedRange top left row is " & UsedRange.Row & ". If all are 1, then that means I either just used someting in the first cell, or more likely, as in this case, I've not used the worksheet yet." & vbCr & vbLf & "If I do use my range then it will have a row count of " & Range("C4", "A1:A10").Rows.Count & " and a column count of " & Range("C4", "A1:A10").Columns.Count & "," & vbCr & vbLf & "(and isn't it nice that sincee the server change we have a horizontal scroll bar on the code window, so I can fill it with stuff, and you don't have to read it unless you feel the urge. I ceratainly don't very often. ( I do occaisionally have an urge or two, but, like most sensible people, I don't read my long posts very often. Well I don't need to do I-they're so good and rich with info that one read and you know it all, and all your problems are solved. :) " & vbCr & vbLf & " :) ;) ) )"
' Now use it
Let Rng2BorNot2B.Value = "Usded"
MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and UsedRange column count is " & UsedRange.Columns.Count & ""
' "Un use it" - I delete it, so Excel trashes it and slides the cells to fill the hole, and puts new ones in the hole then caused at the spreadsheet perimeter.
UsedRange.Delete
MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and Usedrange column count is " & UsedRange.Columns.Count & ""
End Sub
_.________________________________
I personally don’t like that two argument syntax version of
Range( ) because,
_a) It often seems to cause problems when people don’t use it right
_b) I like the single argument syntax version of
Range( ) ,
_b)(i) especially since I realised that the single argument syntax version of
Range( ) lets you define multiple areas,
_b(ii) and also because it will accept in place of the simple address reference, the entire reference string including the full closed workbook path*** , which I often have already defined in my coding anyway, so its convenient to use. That way I use
Application.Range( )
In conjunction with the full reference path. That way there is no ambiguity on what range I am using, regardless of where my coding is.
( ***
It does not work if the workbook is closed, but it accepts the full closed workbook reference with the workbook open )
_._________
Often the main reason for using the two argument syntax version of
Range( ) seems to be when you are dealing with a column number rather than a column Letter. That is most likely what the OP was originally trying to do.
I have got in the habit of having a simple function in most of my files to get the column Letter, so then I can always use the single argument syntax version of
Range( )
So for example, the original erroring code line of the OPs of this
Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents
Would be written something like this, when using the single argument syntax version of
Range( ),
Range("C3,D3,G3,H3").ClearContents =
Range("" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
Code: Select all
Sub ColumnLetterOneArgumentRange() ' http://www.eileenslounge.com/viewtopic.php?p=275972#p275972
Dim lngMyRow As Long: Let lngMyRow = 3
' Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents --- error!!
' Range("C3,D3,G3,H3").ClearContents --- works!!
Range("" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
' Or like Range("Sheet1!C3,D3,G3,H3").ClearContents
Application.Range("=" & ThisWorkbook.Worksheets.Item(1).Name & "!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
' or like Range("=[Book2.xls]Sheet1!C3,D3,G3,H3").ClearContents
Application.Range("=[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets.Item(1).Name & "!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
' or like Range("='C:\Users\Elston\Documents\[Book2.xls]Sheet1'!C3,D3,G3,H3").ClearContents
Application.Range("='" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets.Item(1).Name & "'!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
End Sub
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980 https://excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort-Last-Row?p=7214&viewfull=1#post7214
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also