Mgarza
The easiest way to get the correct code syntax is to record a macro. This is what I recorded:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("C28").Select
Selection.Copy
Range("D28").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B27").Select
Application.CutCopyMode = False
Selection.Cut
Range("D27").Select
ActiveSheet.Paste
End Sub
So now we have to incorporate it into our code. You are stepping up from the bottom, so
---In ExcelVBA@yahoogroups.com, <mgarza@...> wrote :
For i = lrow - 1 To 2 Step -1
If IsEmpty(Cells(i, 3)) Then
'Do nothing
Else
'Cut and paste
Cells(i, 3).Cut
Cells(i, 3).Offset(0,1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next i
I never work with cells, I use ranges insted. So my syntax may be incorrect. Also, check the offset row column setting to ensure you move +1 column. Now, if you record the macro with relative references switched on it will record the offset correctly for you.
VBA is there to assist you automate tasks. You have to know what you want to do in Excel first. And a handy way to do that is to just record the steps.
Paul
Hey Paul thanks for the input but the subtotals are populated as values in the report and not as formulas so I'm not sure the code below would work.
If left(Range(CurrentColumn & i).formula = subtotal then
But this brought my attention to another piece of code I use for something that might work if modified, problem is I do not know how to modify the "Then" argument to cut and PasteSpecial ValuesOnly. See this code and let me know if you see a solution:
lrow = Cells(Rows.Count, 12).End(xlUp).Row
For i = lrow - 1 To 2 Step -1
If IsEmpty(Cells(i, 3)) Then Rows(i).Delete
Next i
In this example I would want to cut and PasteSpecial ValuesOnly to the cell 1 column to the right.
How is this possible?
__._,_.___
Posted by: paul.vermeulen@vulcantech.com.au
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
.
__,_._,___
Tidak ada komentar:
Posting Komentar