Although it is immediately, as shown in the title, we are manually organizing the information currently managed by Excel.
Since the number of target data is enormous, there is a limit in manual work, so it can be processed using VBA
The process is as follows.
1. Delete cells other than"name"characters
2. Deleted single-byte alphanumeric characters
3. Remove "symbol"
4). Delete blank line
■ Example --------------------------------------------- -----------------------------------
A2:<div id ="age">20</div>
A3:<div id ="address">Tokyo</div>
※ As shown in the example, the HTML source is pasted in Excel and we want to extract only the information.
By referring to the website, steps 1 and 2 were realized, but I couldn't find any useful information for steps 3 and 4.
In addition, since VBA itself is a beginner, a button is provided for each process and executed one by one.
I'd like you to teach me the code.
Answer # 1
I wrote a regular expression.
It is supposed to process 100 lines.
Correct the number of lines accordingly.
Delete blank lines after deleting non-double-byte characters from each line.
Please correct the"sheet name"accordingly.
Sub test () Set reg = CreateObject ("VBscript.RegExp") Set sh = Worksheets ("Sheet name") With reg .Pattern = "[\ x01- \ x7E \ xA1- \ xDF]" .IgnoreCase = False .Global = True End With 'Remove all non-double-byte characters For r = 1 To 100 sh.Cells (r, 1) = reg.Replace (sh.Cells (r, 1), "") Next 'Delete blank line For r = 100 To 1 Step -1 If sh.Cells (r, 1) = "" Then sh.Cells (r, 1) .EntireRow.Delete End If Next Set reg = Nothing End Sub
Sorry for the correction after closing the question.
The purpose is to make work more efficient and simple, and I don't think that the efficiency of the code itself is related.
Since the loop seems to be one time, I will put an improved version.
Sub test () Set reg = CreateObject ("VBscript.RegExp") Set sh = Worksheets ("Sheet name") With reg .Pattern = "[\ x01- \ x7E \ xA1- \ xDF]" .IgnoreCase = False .Global = True End With For r = 100 To 1 Step -1 'Remove all non-double-byte characters sh.Cells (r, 1) = reg.Replace (sh.Cells (r, 1), "") 'Delete if blank If sh.Cells (r, 1) = "" Then sh.Cells (r, 1) .EntireRow.Delete End If Next Set reg = Nothing End Sub
- excel vba causes automation error when duplicating sheet
- about excel vba loop
- convert excel vba cells to range
- excel vba multi-page tab addition
- vba - access database from excel and realize lookup field
- about vba (excel) addin macro
- i want to sort excel tabular data with vba
- [excel vba scraping] i want to get the html source of a website
- to find values from a range with excel vba
- vba excel start and end times are split and displayed on separate lines
- excel vba stuck on run-time error '52090' and line 4744
- i have a question about excel vba
- vba - how to perform the same processing as the excel concat function without using the concat function
- about exchanging tables with excel vba
- vba - about excel macro
- java - working with ie in excel vba
- vba - i want to generate another instance of excel and copy the sheet
- php - i want to delete only the numbers surrounded by some symbols
- i want to output a file with null mixed in excel with vba
- with excel vba, the character in the cell is divided and displayed by line break
- python - how to know the arguments of a vba function in a protected excel file
- vba - i want to transfer to a cell that meets two conditions
- vba error occurs after the second time
- vba sheet 1 a7, c7, sheet 2 a2 "2020" 03 "01" numerical value is added and displayed in the cell as "20
- the process is interrupted when i click the button on the web page with vba
- vba - i want to copy the last line and paste it to the last line +1
- vba - using the entirerow property
- vba - i want to divide the sheet by the same data
- about excel vba loop
- vba - how to apply change events to multiple cells at the same time