Need help to combine to VBA Statements

[SamCV]

Hello everyone. This is my second post and I need your help with this two VBA Statements.

By surfing the forum, I found this VBA Code that help me to delete rows with specific text which is entered in Dialog Box (See Code below).


Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String

Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("C65536").End(xlUp))
SrchStr = InputBox("Please Enter A Search location to delete")
Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub


With the second code (cortesy of oeldere) it helps me to align miscontinued rows of data (See code below):

Sub filldata_decending_incolumnA()

'from below to the top (decending)

Range(Range("I2:O2"), Cells(Rows.Count, 9).End(xlUp).Offset(, -1)). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
With Columns("I:O")
.Value = .Value
End With

With Columns("A").SpecialCells(xlCellTypeBlanks).Cells
.EntireRow.Delete shift:=xlUp
End With
If Err.Number <> 0 Then MsgBox "There are no or only empty cells"

End Sub


Can anyone help me to combine this two VBA Codes into a single one?? I am practically new in VBA Codes. I know is not only Copy-Paste and done. I know that programming needs references to start and end with the first statement and continue with next and end it. So this is why I ask for your help.

i am attaching a file to test the new combined VBA code. I will really appreciate the big favor and your ideas.

Sam Cruz.
Matamoros, Mexico.

[HaHoBe]

Hi, SamCV,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[SamCV]

Hello everyone. This is my second post and I need your help with this two VBA Statements.

By surfing the forum, I found this VBA Code that help me to delete rows with specific text which is entered in Dialog Box (See Code below).

Sub DeleteRows() 
Dim c As Range 
Dim SrchRng As Range 
Dim SrchStr As String 

Set SrchRng = ActiveSheet.Range("C1", ActiveSheet.Range("C65536").End(xlUp)) 
SrchStr = InputBox("Please Enter A Search location to delete") 
Do 
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues) 
If Not c Is Nothing Then c.EntireRow.Delete 
Loop While Not c Is Nothing 
End Sub

With the second code (cortesy of oeldere) it helps me to align miscontinued rows of data (See code below):

Sub filldata_decending_incolumnA()
 
'from below to the top (decending)
 
Range(Range("I2:O2"), Cells(Rows.Count, 9).End(xlUp).Offset(, -1)). _
 SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
 With Columns("I:O")
 .Value = .Value
 End With

 With Columns("A").SpecialCells(xlCellTypeBlanks).Cells
 .EntireRow.Delete shift:=xlUp
 End With
 If Err.Number <> 0 Then MsgBox "There are no or only empty cells"
 
End Sub

Can anyone help me to combine this two VBA Codes into a single one?? I am practically new in VBA Codes. I know is not only Copy-Paste and done. I know that programming needs references to start and end with the first statement and continue with next and end it. So this is why I ask for your help.

I am attaching a file to test the new combined VBA code. I will really appreciate the big favor and your ideas.

I Hope this reply meets rule #3 of Forum Rules.

Sam Cruz.
Matamoros, Mexico.

[HaHoBe]

Hi, Sam,

I would have expected that you would have edited the original post instead of reposting but I see that oyu have taken an effort to comply with the rules. Please be aware to use code-tags in the future.

There is an alteration if you enter no search string the first part of the macro will not be executed. And based on the sample you provided I changed some pieces of the code in order to have a shorter running time on the macro:

Sub EF957851()
Dim rngCell As Range
Dim rngSearch As Range
Dim strSearch As String

Set rngSearch = ActiveSheet.Range("C1", ActiveSheet.Range("C" & Rows.Count).End(xlUp))
strSearch = InputBox("Please Enter A Search location to delete")
Application.ScreenUpdating = False
If strSearch <> "" Then
  Do
    Set rngCell = rngSearch.Find(strSearch, LookIn:=xlValues)
    If Not rngCell Is Nothing Then rngCell.EntireRow.Delete
  Loop While Not rngCell Is Nothing
End If

'from below to the top (decending)

On Error Resume Next
Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
With Intersect(ActiveSheet.UsedRange, Columns("K:K"))
  .Value = .Value
End With

Intersect(ActiveSheet.UsedRange, Columns("A:A")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
Application.ScreenUpdating = True

If Err.Number <> 0 Then MsgBox "There are no or only empty cells"

End Sub

You would need to make clear if you only want to run both parts of the macro if a search string has been given. Right now only the first part is skipped.

Ciao,
Holger

[SamCV]

Thank you HaHoBe for this lesson of moderation and compliance with the rules. I am somewhat new in the forum and in learning process. I will give a try to the combined VBA Code and see how it works. I'll let you know as soon as I test it.

Saludos cordiales y hasta pronto.
(Best regards and see you later).

SamCV.
Matamoros, Mexico.

[SamCV]

Hello HaHoBe... I am very sorry for being so late in revieweing your code. It works well, but I missed some important details about this codes and how I currently use them.

The first code let me find and delete certain rows with a special text. In my case, "UNIT TOTAL :"
All rows containg this text must be deleted. This is the first part.

The Second part: (Please view sample file before running the first code)
In this sample. Columns "A" to "H" in row 121 begin the data, and then data continues from columns "I" to "O" in row 122. The second code moves data from Columns "A" to "H" in row 121 to Columns "A" to "H" to row 122 (Columns "A" to "H" in row 121 are now blanks). Then the row 121 goes all blank and get delete it from database.

The goal is to combine the two VBA Codes and perform the actions shown above.
Firstly find and delete rows with text "UNIT TOTAL :"
Secondly align all the rows that contain miscontinued data.

This the main idea of the combination of the two VBA Codes.
I hope that you can help me. This will save me a lot of working time.

Best regards and I'll wait for your reply.

Your friend..
SamCV.

[SamCV]

It worked well!!!
Thank you very much HaHoBe.

Best regards and many thanks!!