0

I am very new to VBA (first time using it today) and I am trying to write a program.

I have a data set and in column C, I want it to look for a certain letter (b) and when it finds that letter, copy the row above it, paste it into the row that the letter was found in, then change that cell in column C back to the original letter (b).

What I've gotten so far looking at various tutorials, but I can't find anything that will copy an entire row above and paste it.

Sub TestProgram()

Dim det As String, Result As String

det = Range(C1, C214511).Value

If det = b Then

Replace(

I wasn't sure what to use the replace function for since I couldn't figure out how to make it replace a full row with the row above it. Maybe I need a different function?

Or maybe I'm just completely lost!

Thanks!

BigBen
  • 46,229
  • 7
  • 24
  • 40
Vince Smith
  • 1
  • 3
    To figure out basic tasks like this, it's helpful to use Excel's macro recorder. Simply start recording a macro, perform the operation(s) you want to do, then stop the recording and look at the source generated in the Macro Editor window. – Ken White Feb 12 '19 at 03:22
  • 2
    ^^^ After recording, you will want to strip the code of `.Select` & `.Active` references. Then try to bend the code to your will! See [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more info – urdearboy Feb 12 '19 at 03:25
  • Missed yu @KenWhite – urdearboy Feb 12 '19 at 03:27
  • 1
    @urdearboy: I've been right here, for 653 consecutive days (according to the counter in my profile). :-) – Ken White Feb 12 '19 at 03:29

1 Answers1

1

You made a good start. This should do the trick (tested). As you've mentioned you're new to VBA, I've commented the code to show what's going on at each line.

Sub testProgram()

Dim lastRow As Long     ' This will be our last active row
Dim lastCol As Long     ' This will be our last active column
Dim ws As Worksheet     ' This will be our worksheet
Dim r As Long           ' This will be used to represent the row number when looping

' Set our worksheet
Set ws = ThisWorkbook.Worksheets(1)     ' Change to whichever sheet number/name you're working on

' Define the last row and last column on the working sheet
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row          ' Change "A" to whichever column you like
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column   ' Change "1" to whichever row you like

' Loop through rows starting at the top
For r = 1 To lastRow Step 1
    ' Check if column C contains the value 'b'
    If ws.Range("C" & r).Value = "b" Then
        ' Grab the row above and copy it to the active row
        ws.Range(Cells(r - 1, 1), Cells(r - 1, lastCol)).Copy Destination:=ws.Range(Cells(r, 1), Cells(r, lastCol))
        ' Reset col C of active row to value 'b'
        ws.Range("C" & r).Value = "b"
    End If
Next r

End Sub
stoicalpirate
  • 250
  • 3
  • 12