Remove Columns, Cut and Paste Column, Delete Rows - Macros

**M12NIX** · 11-12-2015, 06:55 PM

I've been asked by work, after claiming I'm pretty good with Excel to provide a short cut for the team. I thought it'd be pretty straight forward but after six years out of the I.T game, and never really being very good at VBA, I'm struggling. Please somebody help !!!

A report is exported daily that requires manipulation. A number of columns require deleting, one needs to be cut and pasted and then many of the records deleted. As most of the employees are not users of Excel I need to make this as easy as possible for them.

Firstly I need to delete the following columns which I have the code for (I think)

Sub sbVBS_To_Delete_Specific_Multiple_Columns()
Sheets("Sheet1").Range("C:D,K:O,R:V,X:AK,AM:AO").EntireColumn.Delete
End Sub

Then I need to Cut column I (Cuarto) to column B.

Then I need to delete all rows that do not have the room number (Cuarto) starting with 29**. Some cells are blank. Others are room numbers ranging from 101 to 5538.

Finally the last row of the report needs to go. This is never in the same row number but is always the last row.

Any help would be greatly appreciated. I will add attachments of the actual report when it works.

Is this all possible in one macro ?

**LeoTaxi** · 11-12-2015, 08:46 PM

change the red A to the column where is the room

Please Login or Register  to view this content.

Kind regards
Leo

**M12NIX** · 11-13-2015, 07:54 AM

Thanks Leo. Almost there. The columns are exactly where I need them but all of the columns are blank.

**LeoTaxi** · 11-13-2015, 08:00 AM

think this is the reasen

Then I need to delete all rows that do not have the room number (Cuarto) starting with 29**. Some cells are blank. Others are room numbers ranging from 101 to 5538.

this part of the code removes the rowes

Please Login or Register  to view this content.

Kind regards
Leo

**M12NIX** · 11-13-2015, 08:28 AM

Every row has been removed. There are no records. The room numbers go from 2901 - 2934. I just need those starting with 29**

**LeoTaxi** · 11-13-2015, 08:40 AM

in witch column the room numbers are ?

**M12NIX** · 11-13-2015, 08:42 AM

B. (After being switched from I) Thanks for this Leo.

**LeoTaxi** · 11-13-2015, 09:07 AM

So change this part

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

**M12NIX** · 11-13-2015, 09:35 AM

I wish I could do this. Thank you so very much Leo. Is there any way to ensure that 3 digit numbers are excluded also. If I change the 29 to 31 I get the 31** room numbers but I also get the three digit numbers such as 310 but I do not want these.

**LeoTaxi** · 11-13-2015, 09:42 AM

you have to be more specific, cause room 310 is normaliter in 31**

this part looks @ first 2 characters in column B, you can change the 2 to more characters if you want

Please Login or Register  to view this content.

kind regards
Leo

**M12NIX** · 11-13-2015, 09:45 AM

Thank you kind Sir. Thank you so very much.

**LeoTaxi** · 11-13-2015, 09:51 AM

maybe

Please Login or Register  to view this content.

if not, poste the expected result in excel sheet

Kind regards
Leo

**M12NIX** · 11-13-2015, 04:02 PM

Hi Leo. I've just notice that when moving the column from I to B it has replaced the data rather than inserting a new column. Is there a way to do this ?

**LeoTaxi** · 11-13-2015, 05:50 PM

Like this ?

Please Login or Register  to view this content.

**M12NIX** · 11-13-2015, 06:17 PM

Spot on. I cannot thank you enough. Have a good day.

**M12NIX** · 12-07-2015, 02:59 PM

Hey Leo, I'm sorry to pester but I'm at a loss. I need to keep an additional two columns in the report (M & O) and I changed the code below. What am I missing as this isn't working ?!

Sub test()
Dim ws As Worksheet, lr As Long
Set ws = ActiveSheet
lr = ws.UsedRange.Rows.Count
With ws
.Range("C:D,K:L,R:W,X:AK,AM:AO").EntireColumn.Delete
.Range("B:B").Insert Shift:=xlToRight
.Range("K:K").Cut Destination:=.Range("B:B")
.Range("K:K").Delete
.Range("A" & lr).EntireRow.Delete
For x = lr To 2 Step -1
If Left(.Range("B" & x), 2) <> "31" Then .Range("A" & x).EntireRow.Delete
Next
End With
End Sub

**LeoTaxi** · 12-07-2015, 03:10 PM

this part

Please Login or Register  to view this content.

think has to be

Please Login or Register  to view this content.

Kind regards
Leo

Remove Columns, Cut and Paste Column, Delete Rows - Macros

LinkBack

Thread Tools

Rate This Thread

Display

Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Re: Remove Columns, Cut and Paste Column, Delete Rows - Macros

Thread Information

Users Browsing this Thread

Similar Threads

How to create Macros buttons to add or delete columns and rows.

How do I combine multiple columns remove duplicates and paste different worksheet & column

Copy columns, remove duplicates and paste different worksheet and column query

[SOLVED] Remove rows based on duplicates in certain columns, and merge data from another column

Macro to delete certain columns and delete rows based on time in another column

how to remove the blank column (delete rows)

Bookmarks

Bookmarks

Posting Permissions