PDA

View Full Version : Set bottom of variable range



abesimpson
04-03-2008, 08:12 PM
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
04-03-2008, 09:15 PM
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
04-04-2008, 12:46 AM
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



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

Simon Lloyd
04-04-2008, 12:51 AM
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
04-04-2008, 12:55 AM
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
04-04-2008, 12:56 AM
Errrr......good catch Malcolm!

abesimpson
04-04-2008, 03:50 AM
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
04-04-2008, 03:58 AM
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 (http://vbaexpress.com/kb/getarticle.php?kb_id=794)

abesimpson
04-04-2008, 04:31 AM
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
04-04-2008, 05:00 AM
What is MyRng?

abesimpson
04-04-2008, 05:25 AM
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
04-04-2008, 05:41 AM
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!


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