How to Find/Move/Insert from an unknown cell to a known cell in the same workbook

[cynzone]

HI,

Every time I think something is so simple it is not.

I am creating an output txt file and running multiple macros.
One macro deletes blank columns

I have a header record in A1 and nothing in B1-...
I need to keep that header record in A1

When I run the macro deleting blank rows it deletes A1 because there is nothing in B, or C.

I have moved A1 to D402 but when I run the delete rows the A1 data is no longer in D402 put could be anywhere in D depending on how many rows were deleted.

I am trying to be as clear as possible.
I appreciate the code for this as I am stumped.

Thank you so much

 

[Smallman]

Hi cynzone

I am stumped too. Can you upload a sample of your data as any code created will depend on that data.

Thanks

Smallman

 

[cynzone]

Here is a copy of the file.
The object is to reformat Sheet 2 into the master. Formatting as required done. Deleting blank rows and rows with dd or dep or transfer in the number column.
I have been running the macros separately but have them in a run all macro.
The replace header isn't working. I tried to move the header record then run the delete rows macro then put the header back.

This is one way I thought would work but I am open to other solutions as the Header record must remain in Cell A1 when all is done before saving.

Thanks

 

[SirJB7]

Hi, cynzone!
I'm absolutely lost, I'd have prayed for not having downloaded your file.
So let us start over and:
a) Get sure you've gone thru all this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
b) Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
c) Explain what is the relation between the data in worksheets Sheet2 and CFPMaster, since I don't have any clue at all (and I don't even mention the content of cell A1...).
Regards!

 

[Smallman]

cynzone

When you say you want sheet2 to be like the Master do you mean you want to get rid of Cols A & B because that master sheet looks odd to me. So one column? No you can't mean that? Two sheets 'Before' and 'After'. Maybe that is what you have but I can't make it out. Can you clarify?

Take care

Smallman

 

[cynzone]

Hi,
The required formatting is complete.

All I need is for cell A1 not to be deleted when I run the macro to delete empty rows.

Everything else is working properly.

I can run all macros which copies and formatts from Sheet 2 to the master.
This is done. I know you can't see it unless you run one the macros. (I am not sure if they uploaded)

So please assume this is done - contents are moved to the Mastersheet and formatted.....
So I need to
remove contents of A1 and move it to D402. This I have already done with a macro.

Then I run the macro I created to delete empty rows which moves the contents of D402 up to ??? as it depends on how many rows have been deleted (this number will always change.

I have to move the contents of (whereever D402 ends up) back to A1 after all rows have been deleted.

Therefore I need a macro to:

Search and find a cell containing "N12.....(what I have in A1) then move it to A1 by inserting it and shifting rows down one.

Is there anything else I can do to make this clearer?
Thanks
C

 

[cynzone]

Hi, cynzone!
I'm absolutely lost, I'd have prayed for not having downloaded your file.
So let us start over and:
a) Get sure you've gone thru all this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
b) Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
c) Explain what is the relation between the data in worksheets Sheet2 and CFPMaster, since I don't have any clue at all (and I don't even mention the content of cell A1...).
Regards!

Hi,

I think perhaps I gave too much info.
A. I already have a macro that moves data from sheet2 to the CFPMaster and formats it. This works fine.
Then I created a macro to delete empty rows but unfortunately it deleted the 1st Row which is a Header row and needs to stay. The macro looks for empty cells in columns B and C (Row one only has data in A1) so it is therefore deleted when I run this macro.

I do not want to delete this row.

I am looking for a solution. I tried to move it to D402 and then run the macro that deletes rows.

So now I have to find it because once the rows are deleted it is not in cell D402 any longer. I have to find it and insert it back to A1 and move that row down 1

Does this help?

Sorry if I am not explaining properly.
What else can I do? I uploaded a file before any macros are run.

Thanks
c

 

[cynzone]

Find/cut/insert to A1


Here is an example of what I want to do:

Find "

H10248196825296495TheABCCompanyLimited20131105
SM00001 1N"


(You will find this currently in CFPMaster in A1) - I moved it before deleting all the rows with empty cells in columns B and C and I want to put it back after empty rows have been deleted)

Move it to A1 ( inserting the row).

Hope this is okay?
C

 

[Smallman]

You have implied an answer. Why would you move the word when you can just do your actions in vba and leave this preserved word where it is untouched? I just would not do it the way you are planning as there are better ways. Also can we see this code that deletes rows. The whole procedure goes together and the only code in the file is the code to save.

Take care

Smallman

 

[cynzone]

Hi there,
I was just trying to move the word as thta is the solution or work around I came up with as I did not know how to change the macro to delete the rows that had empty cells in columns B and C not to delete row A.
Here is the macro I am using
Sub DeleteRowsNotCheques()
Columns("B").Replace "dep*", "#N/A", xlPart
Columns("B").Replace "dd*", "#N/A", xlPart
Columns("B").Replace "", "#N/A", xlPart
Columns("B").Replace "Transfer", "#N/A", xlPart
On Error Resume Next
Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Columns("C").Replace "", "#N/A", xlPart
On Error Resume Next
Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0


End Sub
Thanks,
I am new to this site and trying to find my way around and such. I did read the Intro.

 

[Smallman]

cynzone

Your code above made more sense to me than all the words you used to describe the process. I made a file to replicate the code above. It might give you some ideas.

Option Explicit
 
Sub AdvDel()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
 
    Range("A1:C" & lr).AdvancedFilter xlFilterInPlace, Sheet2.[A1:A4], , False
    Range("A2:C" & lr).EntireRow.Delete
    Sheet1.ShowAllData
    Range("A1:C" & lr).SpecialCells(4).EntireRow.Delete
End Sub

With the criteria on anothe sheet as it would get deleted otherwise. You might get some ideas as moving data from one place to preserve it is not a great way of coding. If the above doesn't work for you have you thought of putting the contents of A1 into a variable and then just calling that variable after all your code has run.

Anyways hope this helps.

Take care

Smallman

 

[cynzone]

cynzone

Your code above made more sense to me than all the words you used to describe the process. I made a file to replicate the code above. It might give you some ideas.

Option Explicit
 
Sub AdvDel()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
 
    Range("A1:C" & lr).AdvancedFilter xlFilterInPlace, Sheet2.[A1:A4], , False
    Range("A2:C" & lr).EntireRow.Delete
    Sheet1.ShowAllData
    Range("A1:C" & lr).SpecialCells(4).EntireRow.Delete
End Sub

With the criteria on anothe sheet as it would get deleted otherwise. You might get some ideas as moving data from one place to preserve it is not a great way of coding. If the above doesn't work for you have you thought of putting the contents of A1 into a variable and then just calling that variable after all your code has run.

Anyways hope this helps.

Take care

Smallman

Thank you so much.
I am new to this site and to programming (as I am sure you can tell) You have taught me a valuable lesson. I appreciate your help.

Regards,
Cyn

 

[Smallman]

Hey Cyn

I am very happy you found it useful. We will see you next time.

Take care

Smallman