Macro question number.....

March 27, 2009 at 01:42:05
Specs: Windows XP
Hey guys, its me again. This time I have a question relating to the following problem:

I have a macro recorded the sort and set an excel file in a better workable perspective for my co-workers. The problem is that the amount of rows are sometimes bigger than in other cases.

I think I might need to do something with "do until column.cell value = """, but is do not know for sure. I tried this and it doesn't really work. The whole macro is placed here. Thanks already for the help!

Sub macro()
'
' Macro
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Time (GMT)"
Range("G:G,I:I").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=MID(RC[-1],5,3)&""-""&MID(RC[-1],9,2)&""-""&RIGHT(RC[-1],4)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C141")
Range("C2:C141").Select
ActiveWindow.SmallScroll Down:=-15
Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=MID(RC[-2],12,8)+TIME(5,0,0)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D141")
Range("D2:D141").Select
Columns("D:D").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Selection.NumberFormat = "m/d/yyyy"
Columns("D:D").Select
Selection.NumberFormat = "h:mm:ss;@"
Rows("1:1").Select
Selection.Font.Bold = True
Columns("D:D").EntireColumn.AutoFit
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("D8").Select
End Sub


See More: Macro question number.....

Report •


#1
March 27, 2009 at 06:42:43
I'll answer your question here, but please read my next post where I explain the benefits of cleaning up recorded code. By manually cleaning up recorded code, you'll learn a lot and your code will be much more efficient.

To determine the last used cell in a column, you can set a variable equal to the last row that contains data and then use that variable in your code.

Let's say you want to find the last used cell in Column A:

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Internally, the application will start at the bottom of the column and look up until it finds data. The LastRow variable will be set to the first row that contains data when coming up from the bottom of the column. You don't want to start at the top and look down becasue there might be an empty cell somewhere in the middle of the column.

Once the LastRow variable is set, you could then use something like:

Range("A1:A" & LastRow).Copy

If you need to find the last cell in a range of columns where you don't know which column will be the longest at any given time, you could loop through the columns as follows:

'Loop through Columns C - L
  For MyCol = 3 To 10
'Set Temp Variable to last row in current column
   TempLastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
'Replace final variable with larger number if required.
    If TempLastRow > LastRow Then LastRow = TempLastRow
'Loop
  Next


Report •

#2
March 27, 2009 at 06:51:45
I’d like to suggest that you spend some time cleaning up your code after you record something. I say this for three reasons, listed in what I think is a decent order of importance - the last, at least at this point in time - being the most important.

1 - Your code will be much easier to read and follow. Consolidating 3 lines into 1 lets the reader (including you) say "Oh so that's what he's doing!" much easier.

2 - The code will be more efficient which means it will run faster and use less resources. For relatively simple macros this won't make much of a difference, but as your code gets more sophisticated, you will actually be able to see a difference in run-time.

3 - Not everything can be done with the recorder so you need to learn to write code directly. By cleaning up recorded code, you learn the syntax and will be able to write better and more efficient code.

OK, so how would I start cleaning up your code? For starters, you rarely have to select a cell to have the code perform an action on it. In many cases, you can tell VBA, in one line, what to do with a cell or a range.

For example:

Rows("1:3").Select
Selection.Delete Shift:=xlUp

becomes

Rows("1:3").Delete Shift:=xlUp

Rows("1:1").Select
Selection.Font.Bold = True

becomes

Rows("1:1").Select.Font.Bold = True

Note: Some things, like PasteSpecial do do require that the range to be selected.


Without really testing your code because I don't have your data, I simply cleaned up your code based on the syntax. It might not be perfect, but it could look something like this:

Sub macro()
'
' Macro
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Rows("1:3").Delete Shift:=xlUp
Columns("C:D").Insert Shift:=xlToRight
Range("C1").FormulaR1C1 = "Date"
Range("D1").FormulaR1C1 = "Time (GMT)"
Range("G:I").Delete Shift:=xlToLeft
Columns("A:G").EntireColumn.AutoFit
Range("C2").FormulaR1C1 = _
"=MID(RC[-1],5,3)&""-""&MID(RC[-1],9,2)&""-""&RIGHT(RC[-1],4)"
Range("C2").AutoFill Destination:=Range("C2:C141")
Columns("C:C").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").FormulaR1C1 = "=MID(RC[-2],12,8)+TIME(5,0,0)"
Range("D2").AutoFill Destination:=Range("D2:D141")
Range("D2:D141").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Columns("C:C").NumberFormat = "m/d/yyyy"
Columns("D:D").NumberFormat = "h:mm:ss;@"
Rows("1:1").Font.Bold = True
Columns("D:D").EntireColumn.AutoFit
Columns("B:B").EntireColumn.Delete Shift:=xlToLeft
End Sub

After that I would start looking into For-Next loops so you aren't hardcoding the same steps over and over again. I gave you an example of a For-Next loop in my previous post.

Also look at other places where you are doing the same thing more than once. For example, you are AutoFitting a number of single columns. You can consolidate things like that by perform the operation on multiple columns at once:


Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit

becomes

Columns("B:C").EntireColumn.AutoFit

Have fun!



Report •

Related Solutions


Ask Question