excel vba

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA

Excel VBA

resolvedResolved · Urgent Priority · Version 2010

Bin has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Access Intermediate course
Access Advanced course
Excel VBA Advanced course

Excel VBA

Hi when i export data in excel version from the system, the data has been combined in the first column. How can i use VBA to convert the text to different columns? Also is there anyway that it can adjust the column width itself and make it fit to the content? Thanks

RE: Excel VBA

Hi Bin

Thanks for getting in touch. To answer the first part of your question, there are a couple of ways to achieve this. The simplest method would be to record a new macro where you use the Data > Text to Columns process in Excel. You can then customize this as required.

There is also a function called SPLIT in VBA which achieves a similar result. Here is a Microsoft article on the subject:

http://msdn.microsoft.com/en-us/library/6x627e5f (v=vs.90).aspx

With column width, autofit is a method you can assign to any range object. So for example you can do

ActiveCell.Autofit ' autofits the current cell
Columns("A:D").Autofit ' Autofits columns A, B, C and D
Range("A1").CurrentRegion.Autofit ' Autofits all cells connected to cell A1
Cells.Autofit ' autofits all cells on the current sheet

So there's lots of options there. I hope this is of some help, do let me know if you need any further clarification on these features.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA

Hi Gary, do you mind to email me?
Thanks
Bin Chang

RE: Excel VBA

Hi Bin

Thanks for your reply. It's easier to go through the forum as other trainers can reply if I'm not available.

Was there something in the reply you needed further guidance on?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA

Hi Gary,
I have some row data which i wonder if you could have a look and use VBA to convert it into other format. If you coud give me your email i can send them to you
Thanks
B

RE: Excel VBA

Hi Bin

You can email your file to info@stl-training.co.uk

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA

Hi Gary,
Did you received my email? Please let me know.
Thanks
Bin

Edited on Wed 16 Jul 2014, 11:20

RE: Excel VBA

Hi Bin

Your data will be held confidentially and deleted after use. Can you send the file to forum@stl-training.co.uk please?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA

Hi Gary,
I have emailed you the data. Please let me know if you have any progress.
Many thanks
Bin

Edited on Wed 16 Jul 2014, 11:35

RE: Excel VBA

Hi Bin

Thanks for sending that over.

With this data your best bet is to use Text to Columns first (you can record this step) but use the "Fixed Width" option on all of your data in Column A. Then insert and delete column breaks as required. I've attached a screenshot of this.

Then you can write a process that swaps columns of data around with loops. Here's a forum post with some very good starting code on the subject:

http://www.ozgrid.com/forum/showthread.php?t=66005

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

text-to-columns.PNG

RE: Excel VBA

Hi Gary,
Thanks for your reply. Is there anyway i can filter and delete all the rows that are not started with text?
Thanks
Bin

RE: Excel VBA

Hi Bin

Why not create a loop that moves down the rows looking for blank cells? The key will be checking that it is a true empty row and not the end of the data.

The test would look something like this:

IF ActiveCell.Offset(0, 1) = "" AND ActiveCell.Offset(0, 2) <> "" THEN...

This says "if the cell below is empty and the cell 2 rows below is not empty, then..."

You could then finish the command with something like

ActiveCell.Offset(0, 1).EntireRow.Delete

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA

Hi Gary,
Based on our prior conversation on the forum, I have created a macro to handle the raw data saved on the “RawDataBackUp” page of the attached excel file to the mail I sent you today. However, my macro has some issues which I hope if you could help. Please see my questions listed below:

The macro runs very slow. It sorts the data one row at a time. Is there anyway can make it run faster?

In order to remove the specific rows I inserted an extra column with “x”at Column A first. Is there any other way that I don’t need the extra column while it can do the same work?

I want to remove the last three rows of the raw data, every time i run the macro. I created a Dim function which seems not working in the macro, but if I test it separately it works well. Could you please have a look and see why.

How can I rename the field titles as listed below? The field title in the raw data is actually split into two rows. You see if I run the macro the top part will be removed and then there will be two columns with same field title. Don’t know if it matters but is there any way I can replace the old title to the new title as listed below?

I planned to make the macro do in the order as listed below:
1.Remove the top 10 rows
2.Remove column M “Remarks”
3.Remove any row that start with blank cells or “__” or “Cy” or, has blank cells in column D
4.Rename fields name as per my email
5.Adjust the column with

On the spreadsheet I have put the original RawData on the “RawDataBackUp” page. And you can test my Macro on the “TestSheet” page where has the same data.
Please let me know.
Many thanks
Walt

RE: Excel VBA

VBA code i made have listed below:

Option Explicit

Sub RemoveTop10Rows()

Dim iRows As Integer

'remove the top 10 rows
Rows("1:10").Delete

'insert a column in column A and fill with data "x"
Range("a1").EntireColumn.Insert
ActiveCell.EntireColumn.Value = "x"

'go to a2 and use do loop to remove all rows that start with " ","Cy","__" in column B. _
Do until the row starts with "To"
Range("a2").Select

Do Until ActiveCell.Offset(0, 1) = "To"

If ActiveCell.Offset(0, 1) = "" Or _
ActiveCell.Offset(0, 4) = "" Or _
ActiveCell.Offset(0, 1) = "Cy" Or _
ActiveCell.Offset(0, 1) = "__" Then
'remove any row starts with ""
'remove any row starts with figures
'remove any row srates with "Cy"
'remove any row starts with "__"

ActiveCell.EntireRow.Delete

ElseIf ActiveCell.Offset(1, 0).Select Then

End If

Loop

'Remove the inserted column with "x"
Range("a1").EntireColumn.Delete

'go to A1 cell
Range("a1").Select

'use dim to count the number of rows after removed blank rows
iRows = Range("a1").CurrentRegion.Rows.Count

'go to the last row in the data area and remove the (the dim seems not working, but works if i run it separately to only this small session)
Selection.Offset(iRows - 1, 0).EntireRow.Delete
Selection.Offset(1, 0).EntireRow.Delete
Selection.Offset(1, 0).EntireRow.Delete


End Sub

Wed 23 Jul 2014: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Selecting constant values only

If periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter.

View all Excel hints and tips


Server loaded in 0.06 secs.