VBA macro code to find last worked row or column

[getsatv]

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]

[vba]

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

LastCol = Cells(7, Columns.Count).End(xlToLeft).Column
[/vba]

[getsatv]

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

[vba] 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

[/vba]

[R1C1]

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]

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]

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]

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]

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 ...