PDA

View Full Version : VBA macro code to find last worked row or column



getsatv
05-28-2008, 01:46 AM
Hi, Everyone

Please give me the code in macro to find last worked row or column in excel and do the formating for same.

I need a syntax for finding last wored row or column.

Regards
Sat

Bob Phillips
05-28-2008, 02:40 AM
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

LastCol = Cells(7, Columns.Count).End(xlToLeft).Column

getsatv
05-28-2008, 03:15 AM
My friend thank very much!

I have a problem in my macro which i use for formating and deleting some unwanted columns Insert the new column.

Problem is that code is been hard coded it works only form A1:A16.

But everytime by workrange will be changing. Pasting the code here please give ur valubale suggestion on it. to apply the macro till last worked row and column

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "GSC"
Range("A2").Select
ActiveCell.FormulaR1C1 = "IR (SR Header Details)"
Range("A3").Select
ActiveCell.FormulaR1C1 = "for S J DC "
Range("A4").Select
ActiveCell.FormulaR1C1 = "From"
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A5").Select
ActiveCell.FormulaR1C1 = "S/N"
Range("A6").Select
ActiveCell.FormulaR1C1 = "1"
Range("A7").Select
ActiveCell.FormulaR1C1 = "2"
Range("A6:A16").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.AutoFill Destination:=Range("A6:A16")
Columns("C:C").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Rows("5:5").Select
Selection.Find(What:="Pro", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns("J:J").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Rows("5:5").Select
Selection.Find(What:="Exte", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns("Z:Z").Select
Selection.Cut
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Range("J5").Select
ActiveCell.FormulaR1C1 = "Insite Level"
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Rows("5:5").Select
Range("D5").Activate
Selection.Find(What:="Sev", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns("AI:AI").Select
Selection.Cut
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Rows("5:5").Select
Range("D5").Activate
Selection.Find(What:="Pri", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns("AJ:AJ").Select
Selection.Cut
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.ColumnWidth = 13
Columns("M:M").ColumnWidth = 13.71
Range("M5").Select
ActiveCell.FormulaR1C1 = "Resolution Desc"
Range("D5").Activate
Selection.Find(What:="Statu", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Columns("AM:AM").Select
Selection.Cut
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Cells.Find(What:="Date c", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Columns("AP:AP").Select
Selection.Cut
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Columns("P:BB").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Range("A5:O16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Rows("8:8").Select
Selection.RowHeight = 20
Rows("5:16").Select
Selection.RowHeight = 24
With Selection.Font
.Name = "Arial"
.Size = 8
End With
Rows("5:5").Select
Selection.RowHeight = 42.75
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Selection.Font.Bold = True
Selection.Font.ColorIndex = 2
Selection.Interior.ColorIndex = 16
Columns("B:B").ColumnWidth = 10
Columns("O:O").EntireColumn.AutoFit
Columns("N:N").ColumnWidth = 9.14
Columns("M:M").ColumnWidth = 15.86
Columns("L:L").ColumnWidth = 10
Columns("K:K").ColumnWidth = 9.57
Columns("J:J").ColumnWidth = 9.86
Columns("I:I").ColumnWidth = 26.57
Range("I6:I16").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Range("I9").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Columns("H:H").EntireColumn.AutoFit
Columns("G:G").ColumnWidth = 12.86
Columns("F:F").ColumnWidth = 28.57
Rows("6:6").RowHeight = 36
Rows("6:6").RowHeight = 42
Range("D1:F4").Select
With Selection.Font
.Name = "Arial"
.Size = 14
End With
Selection.Font.Bold = True
Columns("A:A").ColumnWidth = 4.71
Columns("A:A").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Columns("I:I").Select
With Selection
.WrapText = True
End With
Rows("6:6").RowHeight = 51
Rows("6:6").RowHeight = 42

R1C1
05-28-2008, 02:29 PM
I looked through your code. It is sporadic at best. You need to clean up the code so it does exactly what you want without the extra steps. At one point you recorded changes the width of Columns("F:F") 13 times. Your code has searches in it and activate it if found but immediately selects a column, so why the search? I would suggest recording the macro again but know exactly what you want to do before you do it. Every time you change your mind, while recording, you are recording your the changes into your macro. You should write down, step by step, what you want to do, then record it step by step. This includes row heights and column widths. After you record it again, remove all lines from the code that start with "ActiveWindow.ScrollColumn =", then repost your code for the variable column help.

Alan

Bob Phillips
05-28-2008, 02:35 PM
I would go one further.

Record it again, but a bit at a time, and post each bit here for us to tidy-up.

Aussiebear
06-09-2008, 03:30 AM
I've just wrapped this code in the VBA tags, and still struggle to understand what the OP is wanting to do with it. I'm not surprised its drawn so little attention to itself.

Simon Lloyd
06-09-2008, 04:27 AM
Gents, i have reposted his code in his post with 90% of the unnecessary removed, there is still too much to 'n' fro in his code it seems the macro recoredr was turned on from the minute he started manipulating his worksheet until the end, i removed all the scrolls and duplicate selects and the formatting code that wasn't needed, i kept his latest values for formatting when there were a string of them!

Best i can do for you - still confusing.

Bob Phillips
06-09-2008, 06:39 AM
I for one am not gooing to try and sort that mess out, I am bound to make mistakes. If he wants to break the problem down and post it one step at a time, then we can help ...