PDA

View Full Version : Resize Range Method



Tezzies
10-23-2008, 03:50 AM
The range is stored in a cell on sheet 2----> $K$5:$P$137

The range refers to cells on sheet 1.


If certain conditions are met i wish to in increase 137 by 1 . What is the best best method.

1. Resize the range by setting it, then using "resize" Then rewriting its dimensions to sheet 2 cell

or

2. Or editing the cell with $K$5:$P$137 in it (I am reluctant to use this method because it would involve detecting the last $ and counting the number of characters afterwards eg 137 = 3 and 45 = 2.)

or

3. A better method you guys will tell me about:hi:


Many thanks

p45cal
10-23-2008, 06:43 AM
Perhaps something on the lines of:
Range("D5").Value = Range(Range("D5").Value).Resize(Range(Range("D5")).Rows.Count + 1).Address

david000
10-23-2008, 02:03 PM
Sub aaa()
Dim wk As Workbook
Dim wSheet1, wSheet2 As Worksheet
Dim Rng As Variant
Dim Str As String

Set wk = ThisWorkbook
Set wSheet1 = wk.Worksheets("Sheet1")
Set wSheet2 = wk.Worksheets("Sheet2")
Rng = wSheet2.Cells(1, 1).Value

If MsgBox("Add another row?", vbInformation + vbYesNo) <> vbYes Then Exit Sub

With Range(Rng)
wSheet2.Cells(1, 1) = .Resize(.Rows.Count + 1).Address
End With

End Sub

Tezzies
10-24-2008, 12:55 AM
Many thanks , all better now.:clap:

georgiboy
10-24-2008, 01:26 AM
Rather than waste it here is what i did, probably a very strange way to go about it.

Sub increaser()
Dim Rng As String, RRng As String
Dim Num As String, RNum As String

Rng = Range("A1").Value
RRng = StrReverse(Rng)
Num = Left(RRng, WorksheetFunction.Find("$", RRng) - 1)
RNum = StrReverse(Num)


If InStr(Rng, RNum) Then Range("A1").Replace What:=RNum, Replacement:=RNum + 1


End Sub


Hope this makes you all laugh :rotlaugh: