Select only a certain range

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Select only a certain range

Logged in as: Guest

arrSession:exec spGetSession 2,2,61369

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Select only a certain range
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Select only a certain range - 6/14/2008 1:15:23 PM

gi_james

Posts: 4
Score: 0
Joined: 6/14/2008
From: Misawa Japan
Status: offline

Greetings all,

I'm trying to automate formatting a spreadsheet. I need to know how to select only a certain range of cells.

I need to select the range of cells between rows one and three. column B would always contain a number like 13529954, and move them to the end of the row above it.

Please look at the spreadsheet below. I have imported a TAB Delimited spreadsheet and formatted the way it is suppsoed to be. I just need some help on automating this. If you look in the formatted spreadsheet, you'll see that Tab B is the formatted one.

Basically, I need to copy the range of cells below the row with a conf. number (i.e. 13529954) and move them next to the time on that row (or move them up one row to the end of that range). Some have mulitple rows that need to be moved, this is where I'm confused. Please look at my code and point me in the right direction.

Here is an example: http://www.strickstuff.com/report_example.xls
here is an example: http://www.strickstuff.com/mycode.txt

Sub OLVIMS_REPORT()
'======================================================
' CLOSED 868 REPORT AUTO FORMAT SCRIPT/MACRO
'======================================================

Columns("A:B").Select
   Selection.Delete Shift:=xlToLeft
   Range("H1").Select
   Cells.Replace What:="END OF REPORT", Replacement:="", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
   Range("A2").Select
   Columns("H:H").EntireColumn.AutoFit
   Columns("I:I").EntireColumn.AutoFit
   Columns("C:C").EntireColumn.AutoFit
   Columns("B:B").EntireColumn.AutoFit
   Range("A2:I2").Select
   Selection.Cut Destination:=Range("H1:P1")
   Range("A4:I4").Select
   Selection.Cut Destination:=Range("H3:P3")
   Range("A6:I6").Select
   Selection.Cut Destination:=Range("H5:P5")
   Range("A8:I8").Select
   Selection.Cut Destination:=Range("H7:P7")
   Range("A10:I10").Select
   Selection.Cut Destination:=Range("H9:P9")
   Range("A12:I12").Select
   Selection.Cut Destination:=Range("H11:P11")
   Range("A14:I14").Select
   Selection.Cut Destination:=Range("H13:P13")
   Range("H13:P13").Select
   ActiveWindow.SmallScroll Down:=9
   Range("A16:I16").Select
   Selection.Cut Destination:=Range("H15:P15")
   Range("A18:I18").Select
   Selection.Cut Destination:=Range("H17:P17")
   Range("A20:I20").Select
   Selection.Cut Destination:=Range("H19:P19")
   Range("A22:I22").Select
   Selection.Cut Destination:=Range("H21:P21")
   Range("A24:I24").Select
   Selection.Cut Destination:=Range("H23:P23")
   Range("A26:I26").Select
   Selection.Cut Destination:=Range("H25:P25")
   Range("H25:P25").Select
   ActiveWindow.SmallScroll Down:=12
   Range("A28:I28").Select
   Selection.Cut Destination:=Range("H27:P27")
   Range("A30:I30").Select
   Selection.Cut Destination:=Range("H29:P29")
   Range("A32:I32").Select
   Selection.Cut Destination:=Range("H31:P31")
   Range("A34:I34").Select
   Selection.Cut Destination:=Range("H33:P33")
   Range("A36:I36").Select
   Selection.Cut Destination:=Range("H35:P35")
   Range("A38:I38").Select
   Selection.Cut Destination:=Range("H37:P37")
   Range("A40:I40").Select
   Selection.Cut Destination:=Range("H39:P39")
   Range("A42:I42").Select
   Selection.Cut Destination:=Range("H41:P41")
   Range("A44:I45").Select
   Selection.Cut Destination:=Range("H43:P44")
   Range("H43:P44").Select
   ActiveWindow.SmallScroll Down:=18
   Range("A47:I47").Select
   Selection.Cut Destination:=Range("H46:P46")
   Range("A49:I49").Select
   Selection.Cut Destination:=Range("H48:P48")
   Range("A51:I51").Select
   Selection.Cut Destination:=Range("H50:P50")
   Range("A53:I54").Select
   Selection.Cut Destination:=Range("H52:P53")
   Range("A56:I57").Select
   Selection.Cut Destination:=Range("H55:P56")
   Range("A59:I60").Select
   Selection.Cut Destination:=Range("H58:P59")
   Range("A62:I63").Select
   Selection.Cut Destination:=Range("H61:P62")
   Range("H61:P62").Select
   ActiveWindow.SmallScroll Down:=21
   Range("A65:I66").Select
   Selection.Cut Destination:=Range("H64:P65")
   Range("A68:I70").Select
   Selection.Cut Destination:=Range("H67:P69")
   Range("A72:I74").Select
   Selection.Cut Destination:=Range("H71:P73")
   Range("A76:I76").Select
   Selection.Cut Destination:=Range("H75:P75")
   Range("A78:I80").Select
   Selection.Cut Destination:=Range("H77:P79")
   Range("H77:P79").Select
   ActiveWindow.SmallScroll Down:=15
   Range("A82:I85").Select
   Selection.Cut Destination:=Range("H81:P84")
   Range("A87:I87").Select
   Selection.Cut Destination:=Range("H86:P86")
   Range("H86:P86").Select
   ActiveWindow.SmallScroll Down:=-99
   
   '
   ' Delete Blank Rows Macro
   '
   On Error Resume Next
 
   Columns("I:I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
   '
   ' auto_width Macro
   '
   '
   Columns("A:Z").EntireColumn.AutoFit
'
' COLOR Macro
'
'
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual   'pre XL97 xlManual
 Dim Rng As Range, ix As Long
 Set Rng = Intersect(Range("P:P"), ActiveSheet.UsedRange)
 For ix = Rng.Count To 1 Step -1
     If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
       Rng.Item(ix).ClearContents
     End If
 Next
done:
 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
   Columns("H:H").SpecialCells(xlCellTypeBlanks).Select
   With Selection.Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .COLOR = 65535
       .TintAndShade = 0
       .PatternTintAndShade = 0
   End With
   Columns("P:P").Select
   Columns("P:P").SpecialCells(xlCellTypeBlanks).Select
   With Selection.Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .COLOR = 65535
       .TintAndShade = 0
       .PatternTintAndShade = 0
   End With

End Sub

_____________________________

James Strickland
Misawa Air Base, Japan
http://www.strickstuff.com