Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
sexy_andrea2006 Profile
 | Femeie
22 ani
Sibiu
cauta Barbat
22 - 48 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Note: this works without VBA, check this topic:
You can use the VBA code below if your Excel version doesn't support the keyboard shortcuts from the topic above, or if those keyboard shortcuts are in conflict with some of your own.
The selection must be done left to right and up to down (starting from the top left corner of range).
After pasting the strings, the code will move to the first row above the selection.
Sub Copy_row_above_to_selection()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim first_col_letter, last_col_letter As String
Dim first_cell_row As Integer
Dim rng_paste As Range
Set rng_paste = Selection
first_cell_row = Selection.Row
' letter of first column in selection
first_col_letter = Split(Cells(Selection.Row, Selection.Column).Address, "$")(1)
' letter of last column in selection
last_col_letter = Split(Cells(Selection.Row, Selection.Column + rng_paste.Columns.Count - 1).Address, "$")(1)
' copy row above selection
ActiveSheet.Range(first_col_letter & Selection.Row - 1 & ":" & last_col_letter & Selection.Row - 1).Copy
' and paste it to selection
rng_paste.PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Range(first_col_letter & first_cell_row - 1).Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
After pasting the strings, the code will select the first cell after the selection, in the first column of the selection.
The added or modified lines of code compared to the above one are colored in light green.
I've set the keyboard shortcut to run this macro to CTRL+W.
Sub Copy_row_above_to_selection()
Dim first_col_letter, last_col_letter As String
Dim first_cell_row As Integer
Dim rng_paste As Range
Dim last_cell_row As Integer
Set rng_paste = Selection
first_cell_row = Selection.Row
last_cell_row = rng_paste.Rows.Count + rng_paste.Row
' letter of first column in selection
first_col_letter = Split(Cells(Selection.Row, Selection.Column).Address, "$")(1)
' letter of last column in selection
last_col_letter = Split(Cells(Selection.Row, Selection.Column + rng_paste.Columns.Count - 1).Address, "$")(1)
' copy row above selection
ActiveSheet.Range(first_col_letter & Selection.Row - 1 & ":" & last_col_letter & Selection.Row - 1).Copy
' and paste it to selection
rng_paste.PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Range(first_col_letter & last_cell_row).Activate
End Sub
|
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Edited post #1 to add info about existing Excel keyboard shortcuts to copy Row to multiple rows and Column to multiple columns.
|
|
| pus acum 2 ani |
|