Build macro routine
Closed
gchancga
Posts
2
Registration date
Wednesday September 25, 2013
Status
Member
Last seen
October 3, 2013
-
Sep 25, 2013 at 02:14 AM
gchancga - Oct 11, 2013 at 03:59 AM
gchancga - Oct 11, 2013 at 03:59 AM
Related:
- Build macro routine
- Spell number in excel without macro - Guide
- Macro excel download - Download - Spreadsheets
- Nasm build - Guide
- Automatically run a macro when opening a worksheet - Guide
- Excel macro to create new sheet based on value in cells - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 26, 2013 at 11:21 AM
Sep 26, 2013 at 11:21 AM
Hi Gchancga,
Not clear.
"copy a row of 3 cells " You mean A1:C1?
Destination: ???
Explain in more detail.
Best regards,
Trowa
Not clear.
"copy a row of 3 cells " You mean A1:C1?
Destination: ???
Explain in more detail.
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 30, 2013 at 10:26 AM
Sep 30, 2013 at 10:26 AM
Hi Gchancga,
Give this code a try:
Best regards,
Trowa
Give this code a try:
Sub RunMe()
For Each cell In Range("A1:A20") 'Adjust range to match yours
If cell.Value <> vbNullString Then
Range(cell, cell.Offset(0, 2)).Copy
Else: cell.PasteSpecial
End If
Next cell
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 1, 2013 at 10:33 AM
Oct 1, 2013 at 10:33 AM
Hi Gchancga,
If you mean that the last cell with content should not be copied down then try this code:
Best regards,
Trowa
If you mean that the last cell with content should not be copied down then try this code:
Sub RunMe()
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & lRow)
If cell.Value <> vbNullString Then
Range(cell, cell.Offset(0, 2)).Copy
Else: cell.PasteSpecial
End If
Next cell
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 3, 2013 at 10:34 AM
Oct 3, 2013 at 10:34 AM
Sorry gchancga, I failed to understand you.
An active cell is the cell you have selected. Do you mean that you want to loop until the pre-selected (active) cell is reached?
If this is indeed what meant then change:
lRow = Range("A" & Rows.Count).End(xlUp).Row
into:
lRow = ActiveCell.Row
Best regards,
Trowa
An active cell is the cell you have selected. Do you mean that you want to loop until the pre-selected (active) cell is reached?
If this is indeed what meant then change:
lRow = Range("A" & Rows.Count).End(xlUp).Row
into:
lRow = ActiveCell.Row
Best regards,
Trowa
Finally figure it out, (just need to loop it).
Sub selectArange()
' selectArange copy and paste Macro
'
'
Dim area As Integer
Range(ActiveCell, ActiveCell.End(xlDown)).Select
area = Selection.Rows.Count
Selection.Resize(area - 1, 3).Select
Selection.FillDown
ActiveCell.Offset(area - 1, 0).Select
End Sub
Sub selectArange()
' selectArange copy and paste Macro
'
'
Dim area As Integer
Range(ActiveCell, ActiveCell.End(xlDown)).Select
area = Selection.Rows.Count
Selection.Resize(area - 1, 3).Select
Selection.FillDown
ActiveCell.Offset(area - 1, 0).Select
End Sub
Sep 26, 2013 at 01:49 PM
Tx for the help! Yes copy A1:C1, to all the blanks cells below and stop at the next non-blank cell, and start the routine again, copy from those 'non-blank' cells downwards to all the blank cells, and the loop stops when it reaches a blank content! My macro recording seem to always copies over the non-blank cell, I just need the paste range to move back up one row, right?
Sep 30, 2013 at 01:10 PM
Thanks for the suggestion, it's a good start! One question, I do not know where my paste destination ends? It should end immediately before the next cell with content! I think I need to test for the last blank cell down the column and store it in a variable to use for range parameter?
Tx