Cut & Paste VBA

Angus95

New Member
Joined
Jun 19, 2019
Messages
11
Hi,

I am new to VBA, getting a bit of a hang of it.

I found this code via forums etc.
Rich (BB code):
Sub Delete_Data()
Dim i As Long, lastrow As Long, mydate As Date, erow As Long
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
Application.ScreenUpdating = False
For i = lastrow To 2 Step -1
mydate = Cells(i, "C")
If mydate > DateValue("april 20, 2019") Then
Cells(i, "c").EntireRow.Cut
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If


Next i
Delete_Blank_Rows


End Sub

Sub Delete_Blank_Rows()
Dim row As Long
lastrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).row
row = 2
For row = row To lastrow
If Cells(row, 1) = "" Then
Cells(row, 1).EntireRow.Delete
End If
Next row
End Sub

It works to remove rows with dates after a certain value.

I am looking to make it so it does this but with text values / names.

Firstly, can someone help explain parts of the code that I have highlighted in red that I don't exactly understand.

For example 'AS Date', can that be changed to 'AS Text' for example?

Basically I am wanting to do what this code does but if the cell equals completed then cut and past the entire row to the worksheet 2.

Cheers
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi and welcome to the board,

dim i as long
dim strText as string
dim lngRow as long
dim bolTrue as boolean

the "dim" statement means you declare a variable in your code it must be in your code to be able to declare a variable!
the as means you declare your variable as a datatype
datatype is what values your variable can "hold" what datatype it can "store" so if you like to be able to store a "text" then it needs to be declared as a "string"
So something like "dim strMyName as string"

But to get a bit of a better idea about vba watch some youtube videos there are tons out there for beginners a good starting point woule be "wiseowl tutorials vba"

hope this will help you to get a better understanding.
 
Upvote 0
These as VBA variables to use in your code and as Silentwolf said they can be assigned a Datatype.

Check out this playlist about VBA basic on youtube, I found it extremely helpful when learning the basics.
https://www.youtube.com/playlist?list=PLpOAvcoMay5SE3XTp2YN2v6NcJuXKM9KX

dim (declares a variable) variableName (The Variable's Name) as (assigns it a datatype) Long (chooses the Datatype, this example is Long, a number between -2,147,483,648 to 2,147,483,648)

VBA has several Datatypes - Check this article all about VBA variables
https://www.guru99.com/vba-data-types-variables-constant.html
 
Upvote 0
How about
Code:
Sub Angus95()
   With ActiveSheet
      .Range("A1:C1").AutoFilter 3, "Completed"
      With .AutoFilter.Range.Offset(1).EntireRow
         .Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
         .Delete
      End With
      .AutoFilterMode = False
   End With
End Sub
This assumes that "Completed" is in col C.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top