Clicky

I have the attached code and it works to a point.  I need to extend the selection range from column A to Column L AFTER the rngSelect is determine.  This actually grabs the range of blank cells on the worksheet first, and then I need to grab the columns.  I tried making the offset (01,11), but that did not work.
 Sub California() ' California Macro Dim rngSelect As Range     Sheets("CA  ").Select     Cells.Select     Selection.ClearContents     Range("A1").FormulaR1C1 = "STATE"     Range("B1").FormulaR1C1 = "BR"     Range("C1").FormulaR1C1 = "ACCOUNT"     Range("D1").FormulaR1C1 = "CIF NAME"     Range("E1").FormulaR1C1 = "AVG BAL"     Range("F1").FormulaR1C1 = "CUR BAL"     Range("G1").FormulaR1C1 = "INT Rate"     Range("H1").FormulaR1C1 = "INT Earned"     Range("I1").FormulaR1C1 = "S/C ASSD"     Range("J1").FormulaR1C1 = "S/C CALC"     Range("K1").FormulaR1C1 = "NET REMTTD"     Range("L1").FormulaR1C1 = "STATUS"     Range("A1:L1").Select     With Selection.Interior         .Pattern = xlSolid         .PatternColorIndex = xlAutomatic         .ThemeColor = xlThemeColorDark1         .TintAndShade = -0.149998474074526         .PatternTintAndShade = 0     End With     Sheets("All States").Activate     Range("A2").Select     Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select        Set rngSelect = Range(Cells("A2"), Cells(Rows.Count, "A").End(xlDown).Offset(-1, 0))                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 

Select allOpen in new window

asked 12/16/2011 02:46

ssmith94015's gravatar image

ssmith94015 ♦♦


6 Answers:
Try

.resize(,12)
link

answered

ssaqibh's gravatar image

ssaqibh

Ok, now I get Object Required on the     Set rngSelect = Range(Cells("A2"), Cells(Rows.Count, "A").End(xlDown).Offset(-1, 0)) line.  This is getting very frustrating as it works and then does not, so I can't even try the resize as it now does not get past this line.

Sandra
link

answered 2011-12-16 at 10:54:17

ssmith94015's gravatar image

ssmith94015

Got it to work.  Deleted the rngSelect and below is what I have:

    Sheets("All States").Activate
    Range("A2").Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    Selection.Resize(, 12).Select
link

answered 2011-12-16 at 11:01:33

ssmith94015's gravatar image

ssmith94015

Next Line:
Set rngSelect = rngSelect.Resize(, 12)

As for your Object Required error, what are you trying to do with that code?  
link

answered 2011-12-16 at 11:08:11

wabashbdw's gravatar image

wabashbdw

wabashbdw, I was tring to set the range that had been highligh from Cell A2 to the first non-blank cell in columns A.  However, I could not get it to set the range, but this solution is what I am going with.

Sandra
link

answered 2011-12-16 at 11:09:04

ssmith94015's gravatar image

ssmith94015

Sounds good...glad you got it working!
link

answered 2011-12-16 at 11:19:22

wabashbdw's gravatar image

wabashbdw

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/16/2011 02:46

Seen: 248 times

Last updated: 12/16/2011 03:08