Set bottom of variable range

[abesimpson]

I currently have a range hard coded as

RangeTwo = "A2:E11" 'Data Range

I would like to modify this to have the E11 range adjust to suit the number of record, I have tried to replace the above with:

LastRow = Range("E" & Rows.Count).End(xlUp).Row
RangeTwo = "A2:E & lastRow"

Obviously (or I would not be writing) without success. Any help is appreciated.

a

[rbrhodes]

hi,

Search this forum for 'Dynamic named ranges'

or do a google on 'dynamic named ranges'

Lot's of stuff about it out there. Just keep trying till you find an example you understand!

[mdmackillop]

[VBA]
Sub EndRange()
Dim LastRows As Long
Dim RangeTwo As Range

LastRow = Range("E" & Rows.Count).End(xlUp).Row
Set RangeTwo = Range("A2:E" & LastRow)
'or
Set RangeTwo = Range(Cells(2, "E"), Cells(Rows.Count, "E").End(xlUp))
End Sub

[/VBA]

Using a dynamic range name in the Define Name box
=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$E:$E)-1,1)

[Simon Lloyd]

Definately go with the Dynamic Named range because you can call on it both in code and on the worksheet in formula...a much smarter way to work!

[mdmackillop]

Definately go with the Dynamic Named range because you can call on it both in code and on the worksheet in formula...a much smarter way to work!

A word of caution. It only works with solid blocks of data. No gaps allowed. You can cater for known spacing at the head of the column in the offset function, eg, if your header/data started in E5, but not for random gaps elsewhere.

[Simon Lloyd]

Errrr......good catch Malcolm!

[abesimpson]

My problem is that when I try your examples or any other I've found I get errorS, I think from the fact that "RangeTwo" is already defined as a string.

Here is the rest of my code (scooped from this site, modified by me):

Sub MakeXML2() ' create an XML file from an Excel table

Dim MyRow As Integer, MyCol As Integer, Temp As String, DefFolder As String
Dim XMLFileName As String, XMLRecSetName As String, RTC1 As Integer
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String

DefFolder = "C:\" 'File Location

XMLFileName = "XMLFileName.xml" 'File Name

XMLRecSetName = "record"

RangeOne = "A1:E1" 'Field Names

MyRow = MyRng(RangeOne, 1)
For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)
FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol

RangeTwo = "A2:E11" 'Data Range <<<<<<< LINE IN QUESTION!!!

RTC1 = MyRng(RangeTwo, 3)

XMLFileName = DefFolder & XMLFileName

Open XMLFileName For Output As #1
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>"
Print #1, "<hip2b2>"

For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)
Print #1, "<" & XMLRecSetName & ">"
  For MyCol = RTC1 To MyRng(RangeTwo, 4)
  ' the next line uses the FormChk function to format dates and numbers
     Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(FormChk(MyRow, MyCol)) & "</" & FldName(MyCol - RTC1) & ">"
  ' the next line does not apply any formatting
    'Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & "</" & FldName(MyCol - RTC1) & ">"
    Next MyCol
 Print #1, "</" & XMLRecSetName & ">"

Next MyRow
Print #1, "</hip2b2>"
Close #1
End Sub

Thanks in advance

a

[mdmackillop]

From the looks of this
RTC1 = MyRng(RangeTwo, 3)
RangeTwo needs to be a number, not a string or a range.
Have a look at this

[abesimpson]

Interesting but I don't see how I can use this in my problem (my ignorance is almost 100%).

I will keep trying though; infinite monkeys, given infinite time... you know the rest.

Thanks for the help.

a

[mdmackillop]

What is MyRng?

[abesimpson]

Function MyRng(MyRangeAsText As String, MyItem As Integer) As Integer
' analyse a range, where MyItem represents 1=TR, 2=BR, 3=LHC, 4=RHC

Dim UserRange As Range
Set UserRange = Range(MyRangeAsText)
Select Case MyItem
 Case 1
 MyRng = UserRange.Row
 Case 2
 MyRng = UserRange.Row + UserRange.Rows.Count - 1
 Case 3
 MyRng = UserRange.Column
 Case 4
 MyRng = UserRange.Columns(UserRange.Columns.Count).Column
End Select
Exit Function

End Function

Other functions used in the macro:

Function FillSpaces(AnyStr As String) As String
' remove any spaces and replace with underscore character
Dim MyPos As Integer
MyPos = InStr(1, AnyStr, " ")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "_"
 MyPos = InStr(1, AnyStr, " ")
Loop
FillSpaces = LCase(AnyStr)
End Function

Function FormChk(RowNum As Integer, ColNum As Integer) As String
' formats numeric and date cell values to comma 000's and DD MMM YY
FormChk = Cells(RowNum, ColNum).Value
If IsNumeric(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "#,##.####0;(#,##.####0)")
End If
If IsDate(Cells(RowNum, ColNum).Value) Then
 FormChk = Format(Cells(RowNum, ColNum).Value, "dd mmm yy")
End If
End Function

Function RemoveAmpersands(AnyStr As String) As String
Dim MyPos As Integer
' replace Ampersands (&) with plus symbols (+)

MyPos = InStr(1, AnyStr, "&")
Do While MyPos > 0
 Mid(AnyStr, MyPos, 1) = "+"
 MyPos = InStr(1, AnyStr, "&")
Loop
RemoveAmpersands = AnyStr
End Function

Thanks

a

[mdmackillop]

I would get values from all your functions first and use these variables. They are then visible for debug checking.
You would, of course, delare tmp1-4 properly and give meaningful names!

[vba]
Sub MakeXML2() ' create an XML file from an Excel table
Dim MyRow As Integer, MyCol As Integer, Temp As String, DefFolder As String
Dim XMLFileName As String, XMLRecSetName As String, RTC1 As Integer
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String
Dim Tmp1, Tmp2, Tmp3, Tmp4

DefFolder = "C:\" 'File Location
XMLFileName = "XMLFileName.xml" 'File Name
XMLRecSetName = "record"
RangeOne = "A1:E1" 'Field Names
RangeTwo = "A2:E11" 'Data Range <<<<<<< LINE IN QUESTION!!!

MyRow = MyRng(RangeOne, 1)
Tmp1 = MyRng(RangeOne, 3)
Tmp2 = MyRng(RangeOne, 4)
Tmp3 = MyRng(RangeTwo, 1)
Tmp4 = MyRng(RangeTwo, 2)
RTC1 = MyRng(RangeTwo, 3)

For MyCol = Tmp1 To Tmp2
FldName(MyCol - Tmp1) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol
XMLFileName = DefFolder & XMLFileName
Open XMLFileName For Output As #1
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & "?>"
Print #1, "<hip2b2>"
For MyRow = Tmp3 To Tmp4
Print #1, "<" & XMLRecSetName & ">"
For MyCol = RTC1 To MyRng(RangeTwo, 4)
' the next line uses the FormChk function to format dates and numbers
Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(FormChk(MyRow, MyCol)) & _
"</" & FldName(MyCol - RTC1) & ">"
' the next line does not apply any formatting
'Print #1, "<" & FldName(MyCol - RTC1) & ">" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & _
"</" & FldName(MyCol - RTC1) & ">"
Next MyCol
Print #1, "</" & XMLRecSetName & ">"
Next MyRow
Print #1, "</hip2b2>"
Close #1
End Sub

[/vba]