Code:
' Delete One Row From A 2D Excel Range Area
' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17
' "Short hand", ShtHd, version using []
' requires snb and kalak "neat trick" so you can to all intents and purpose do very close to doing vba Un Hard coded in [ ] - http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714 http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html#post4331217
' and Evaluate [ ] Properties , AloPerties, Methods - Alan Dynamic Coding Wonks http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html#post4400666
Function FuR_AlanShtHd(ByVal rngIn As Range, ByVal FoutRw As Long) As Variant
1 Let rngIn.Name = "snRgNme"
5 Dim vTemp As Variant 'A varyable to fill with something and "suck and see" what you get
10 ' use "neat magic" code line arrOut() = Application.Index(arrIn(), rwsT(), clms()) ' http://www.excelforum.com/excel-new-...ba-arrays.html http://www.mrexcel.com/forum/excel-q...ml#post4375354
20 ' BUT in Cells form arrOut() = Application.Index(Cells, rwsT(), clms()) ' http://www.excelforum.com/excel-prog...t-range-2.html
30 Dim ws As Worksheet ' ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense )
40 Set ws = rngIn.Parent ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed. http://www.excelforum.com/excel-prog...ml#post4387191
50 ws.Range("K30").ClearContents: ws.Range("K30").Value = "Here I am, in this Worksheet!"
60 'clms()
70 Dim sClm As Long, Cs As Long 'Variable for Count of, Start Column. - This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for when the variable is filled in. (Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647). If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.-upon/after 32-bit, Integers (Short) need converted internally anyway, so a Long is actually faster)
80 Let sClm = [=MIN(column(snRgNme))] '###Not needed now. Alternative using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
90 Dim clms() As Variant ' Evaluate Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
95 Let clms() = [column(snRgNme)] ' snb Range Name equivalent so ### 'Let clms() = Evaluate("column(" & CL(sClm) & ":" & CL(sClm + (Cs - 1)) & ")")
100
160 'rwsT()
170 Dim sRw As Long, Rs As Long, stpRw As Long '
180 Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate ' Let sRw = rngIn.Areas.Item(1).Row
190 Let Rs = [rows(snRgNme)] 'Let Rs = rngIn.Areas.Item(1).Rows.Count
191 Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
200
240 'Get Full row indicies convenientally ( As 1 D "pseudo horizontal" Array ) from Spreadsheet Column() Function
250 Dim rws() As Variant: Let rws() = Evaluate("column(" & CL(sRw) & ":" & CL(sRw + (Rs - 1)) & ")")
251 Let vTemp = [CL(1)]: vTemp = [CL(MIN(Row(snRgNme)))] 'Both Return "A"
252 vTemp = [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] 'Returns "J"
254 Let rws() = [column(A:J)] ' Works
257 'Let rws() = [column(CL(1):J)] ' Fails - Bug in Excel ! ? !
258 'Let rws() = [column(CL(MIN(Row(snRgNme))):CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1))] ' Fails - Bug in Excel ! ? !
260 Let rws() = Evaluate("column(" & [CL(MIN(Row(snRgNme)))] & ":" & [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] & ")")
270
280 'Get full sequential row indicies in a string.
290 Dim strRws As String: Let strRws = VBA.Strings$.Join(rws(), "|") ' 'The VBA strings collection such as Join in there basic form must not returnn a string, they can also return for example Null, a special type of variant. That lies within it's "powers. - It will coerce even an Empty, or Null to a variant type and return that. That takes extra ( unecerssary work here ). If the result of a function is used as a string or assigned it to a string variable, use the $ form of the function. This results in faster executing code, because a conversion from a variant to a string is unnecessary. http://www.excelforum.com/excel-new-...ml#post4084783 I believe that without $ a Strings collection Function coerces the first parameter into Variant, with $ does not - that's why $ is preferable over no $ , it's theoretically more efficient. http://www.xoc.net/standards/rvbacc....rSignFunctions
300
330 'Get String with missing row
340 Dim strrwsD As String: Let strrwsD = Replace(strRws, "|" & FoutRw & "", "", 1, -1)
350
360 'Get Array ( 1 D Pseudo Horizontal ) of required row indicies
370 Dim rwsS() As String ' The VBA Strings Collection Function, Split, used below returns a Field of String Element Types so the Array Elemments must be Declared appropriately. It must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
375 Let rwsS() = VBA.Strings$.Split(strrwsD, "|", -1)
380 'final Transposed Array for "magic neat" code line
390 Dim rwsT() As String: ReDim rwsT(0 To (UBound(rwsS())), 1 To 1) ' Both the type and size of Array is known so can be decared initially appropriatelly. Re Dim must be used as Dim only takes values, not variables
400 Dim Cnt As Long
410 For Cnt = 0 To UBound(rwsS())
420 Let rwsT(Cnt, 1) = rwsS(Cnt)
430 Next Cnt
440 'Output Array
450 Dim arrOut() As Variant
460 Let arrOut() = Application.Index(ws.Cells, rwsT(), clms()) '"Magic neat" Code line in Cells first argument Form
470
480 Let FuR_AlanShtHd = arrOut()
490
500 ' . Transpose
510 Dim rwsDotT() As Variant ' Transpose Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
520 Let rwsDotT() = Application.Transpose(rwsS())
530 Let arrOut() = Application.Index(ws.Cells, rwsDotT(), clms())
540
550 Let FuR_AlanShtHd = arrOut()
'
End Function
Reply With Quote
Bookmarks