-2

I have an Excel file with multiple sheets. I need a macro that does the following.

  1. Select a sheet with the specified sheet name.
  2. Select all rows from the sheet where length of 1st column data is greater than one
  3. Export the selected data as a CSV file in a specified directory.

I have a very basic understanding of Excel VBA and any help would be appreciated.

Thanks,

Community
  • 1
  • 1
Udit Poddar
  • 1
  • 1

2 Answers2

0

I'll provide you with some sample code. Since we are not here for writing whole code, it will be your task to go deeper and put the code together.

1. If you want to select sheet, you need to call Select method of sheet object. There are multiple ways of referencing sheets, but most common (IMO) is Sheets("Name of the sheet").Select.

2a. For selecting cells, the procedure is the same, but instead of sheets, you have to use cell object, i.e.: Cells(r, c).Select (again, this isn't the only ways of referencing cells, another one: Range("A1").Select), where r and c are integers representing row and column respectively. Generally, you need to call Select on object of Range type.

SUMMARY

So far, I covered how to select certain object in the workbook, but it's not optimal way of doing it. Unless you need to select thing explicitly, I wouldn't do it. Instead you can get value of certain cell in certain sheet using following syntax:

Sheets("SheetName").Cells(r, c).Value

This is combined previous parts of code, which accesses value of a cell in row r and column c in sheet named SheetName, without selecting anything. This way you can either set or get the value.

2b. If you want to test something, like condition, use If statement. In your case it would look like:

If Len( Sheets("SheetName").Cells(r, c).Value ) > 1 Then
    'do something if it's true
Else
    'do something if it's false
End If

3. You would have to create some string variable, something like Dim content As String where you'd append all data from cells that passed your test. At the end, after all, you could write this text variable to a file. This might be useful: How to create and write to a txt file using VBA

Additionally, you need to loop through all rows, so you need loop statements, go here for more details: Loops in VBA

I hope that will give you some clue about what to do and how to do :)

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
0

This should get you pretty close to where you want to be.

Public Sub SaveWorksheetsAsCsv()
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "\" & xWs.Name, xlCSV
Next
End Sub
ASH
  • 20,759
  • 19
  • 87
  • 200